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 16 hours 42 minutes ago

Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
With Calculated Column.
http://www.mediafire.com/view/3hr4dwgdj6njy9t/07_27_15.xlsx

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 6 hours 56 minutes ago

It's supposed to be a nudge.
If you want me to do your whole file,
share it at your favorite file sharing site.
Each additional data like customer and invoice and its format
will result in unique, custom solutions.

Ok. I will share it soon

cheers!! 

Here it goes. 

I made it simple. about 4 MB.

https://dl.dropboxusercontent.com/u/99855624/Active%20Licence%20Sample.xlsx

the formular I written above is wrong.

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