Hi 

I have a DAX question for Powerpivot 2013. 

I have a sales transaction table with licence start date and licence end date for each line of transaction.

My goal is to calculate active no of licence at daily basis. for example, when I place date from date dimension on Rows, it should display like

Date                 ActiveLicence          Caculated by
Jan/2/2014      10
Jan/3/2014       10
Jan/4/2014      10
Jan/5/2014       12
Jan/6/2014        12
Jan/7/2014        11
and so on 

Active licence should be calculated by

sum of licences from all the invoices with licence start date >= Date (from date dimension) and Licence end date <= Date

Is it possible to do this in DAX?

I have 3 date dimension: Invoice Date dimension, Licence Start date dimension, Licence End date Dimension with a sales fact table with the 3 dates and no licence purchased column.

Can anyone help me with this please?

• Edited by SQLMa Monday, July 27, 2015 2:48 PM

Hi Herbert

Thank for you for the reply. Unfortunately, I could not open your file. However, I looked at the file in the browser and saw the example you created. Two things I'd like to point out

1. More than 1 licence purchase per customer

2. It needs to be calculated from sales invoices. Same customer could purchase additional licences during the licence period.

 

Still, I think what you have done could give me a direction. Can you re-share the file with me please? 

Cheers!

• Edited by SQLMa Tuesday, July 28, 2015 12:35 AM

Excel 2013 Pro Plus.
Pruned irrelevant data.
Divorced from external sources.
With Related() and same method as before.
http://www.mediafire.com/view/18p88c7yacbo11l/07_28_15b.xlsx

Hi Herbert

Thank you very much for taking time to look into this.

Unfortunately, it's not the answer I am after.

If you look at the screenshot below, for 8/22/14, sum of all the invoices with License start >= 08/22/14 and License end <= 08/22/14is -174,111.

The hit column only relates to the current row of the invoice information. It should cover all the invoices for every date.

The formula I came up with so far is

=CALCULATE(

      [#Total Licence Sold],

         FILTER(

             All ('TableSales'),

             TableSales[LicenceStartDate] <= LASTDATE('InvoiceDate'[FullDate]) && 'PP Invoice'[LicenceEndDate] >= LASTDATE(' InvoiceDate'[FullDate])

        )

)

The idea was to have InvoiceDate on Row of pivot table and have option ticked 'show items with no data on rows'. It will display all the rows of InvoiceDate. Then with 'All(TableSales), I was hoping to remove all the filters of sales table and apply the filter with the logic of Invoice Date between license startdate and licence end date.

 Obviously this formula is not working.

Got your number (174111, 08/22/14) by adding another short date table.
Also got numbers for 4 other selected dates.
Since this method is not practical and can't be automated,
I suggest using a DAX query with a linked-back table and a VBA macro.
In either case, with more dates you will soon run out of Excel resources.
See same link.

It's working now. Created a ActiveLicenceDate date table without a relationship. 

You are right about 'Date'. I just need to warn users about it and make sure to filter date range. 

It can work without the first Calculate. 

=CALCULATE(

    CALCULATE(

        [#Total Licence Sold],

        FILTER( 

             VALUES('Sales'[LicenceStartDate]),

             'Sales'[LicenceStartDate] <= Max(ActiveLicenceDate[ActiveLicenceDate])

            ),

       FILTER(

            VALUES('Sales'[LicenceEndDate]),

           'Sales'[LicenceEndDate] >= Max(ActiveLicenceDate[ActiveLicenceDate])

          ) 

     ),

   FILTER (

      Values(ActiveLicenceDate[ActiveLicenceDate]),

      ActiveLicenceDate[ActiveLicenceDate] >= Max('Sales'[LicenceEndDate]) 

    )

)

• Marked as answer by SQLMa 6 hours 54 minutes ago