DAX SCD Get amount between date from and date to

[dpombal]

I am working with contracts data, this contract include a measure debt_amount. I should require to have the sum of amount of debt in a selected date.

My data( 5 contracts with contract_id from 1 to 5, with  date from, date to and version columns to create a SCD , and amount of debt as KPI)

Since there is an active flag, calculating current number of active contracts and current amount of debt is easy:

Current measures

amount_total = SUM(Contracts[amount_of_debt])
amount_active=CALCULATE([amount_total] , Contracts[active_flag]=1)

contracts_num_total = DISTINCTCOUNT(Contracts[contract_id])
contracts_num_active = CALCULATE([contracts_num_total] , Contracts[active_flag]=1)

My report ( I created a calendar table to check the debt available this days)

sdas

I need to create a measure which given 1 date, calculates the amount of date available this date.

SUM Debt = CALCULATE ( [amount_total]  ; Date_from <= Given_Date  AND Date_to <= Given_Date )

Date limits and versioning (

 If for a given contract there are 2 values  for a date limit ( get the one with max of version id )

Debt 07/08/2017  (7 august 2017)-->2000€

Debt 08/08/2017 (8 august 2017)- -->3000€ (version 3 of this contract)

 Here it is the PBIX file (https://1drv.ms/u/s!Am7buNMZi-gwkT0R9zM7HqrV2xmx)

I need something like

yyyy/mm/dd --> amount this day €

YEAR 2014 , 0 contracts active

2014/01/01 --> 0€

...

2014/02/01 --> 0€

...

YEAR 2015 , new contract_id =5 (1 active contract)

2015/01/01 --> 1000€

2015/02/01 --> 1000€

2015/03/01 --> 1000€

2015/03/15 --> 2000€

2015/04/01 --> 2000€

...

2015/12/31 --> 2000€

YEAR 2016 , new contract_id =3 ( 2 active contracts)

2016/01/01 --> 2300€

.... and so on

[v-xjiin-msft]

Hi @dpombal,

Did you mean something like this?

https://1drv.ms/u/s!AlqSnZZUVHmshW2Fa2iQGGF5053j

If not, please make a more detailed explaination about your requirement.

Thanks,
Xi Jin.

[dpombal]

I can't download your file please share or make it public in OneDrive

[v-xjiin-msft]

Hi @dpombal,

Sorry. Check this:

https://1drv.ms/u/s!AlqSnZZUVHmshW_cxZuStzh8Lz0R 

Thanks,
Xi Jin.

[dpombal]

Your solution is not valid for me, I need a measure  which for a given date.

Say for example 1 January 2016 gets the sum of all contracts with  this date between the range DATE_FROM and DATE_TO.

In my example for the 1st of January , sum will be 2300 . (2000 + 300). Imagine a Type 2 dimension with date from date to ranges, this is my use case.

something like this..but for my usecase

https://blog.gbrueckl.at/2012/02/handling-scd2-dimensions-and-facts-with-powerpivot/

Regards

[v-xjiin-msft]

Hi @dpombal,

Check this:

SUM Debt =
CALCULATE (
    [amount_total],
    FILTER (
        Contracts,
        Contracts[date_from] <= MAX ( 'Date'[Date] )
            && Contracts[date_to] >= MAX ( 'Date'[Date] )
    )
)

https://1drv.ms/u/s!AlqSnZZUVHmshXIudzkJsDi7Hq9t 

Thanks,
Xi Jin.

[vamsim]

Hi @v-xjiin-msft,

Can we apply this solution using Aggregate tables ? if yes can you please explain how we can accomadate this on-the-fly measure calculation on Aggregate tables? I have tried using the aggregate tables, but the couldn't configure this DAX measure as Detail Column

Thanks,
Vamsi

[dpombal]

Hi @v-xjiin-msft , your solution is not bad , however fails on days of time range change

Check for contract_id 5, 15 of March 2015 is suming (1000 + 2000 and creating a spike), while correct amount for this day is version 2 of this contract  and sum of amount debt should be 2000 instead of 3000.

I managed to solve this with this version 2 formula changing >= by > in date_to:

SUM Debt v2 =
CALCULATE (
    [amount_total];
    FILTER (
        Contracts;
        Contracts[date_from] <= MAX ( 'Date'[Date] )
            && Contracts[date_to] > MAX ( 'Date'[Date] )
    )
)

However instead of my solution , I will prefer something like given a date and a contract , get only the sum of debt amount of the contract line with MAX version.

I tried with no success with this formula

SUM Debt v3 =
CALCULATE (
    [amount_total];
    FILTER (
        Contracts;
        Contracts[date_from] <= MAX ( 'Date'[Date] )
            && Contracts[date_to] >= MAX ( 'Date'[Date] )
            && Contracts[version] =  MAX(Contracts[version])
    )
)

Here it is my updated PBIX https://1drv.ms/u/s!Am7buNMZi-gwkT0R9zM7HqrV2xmx

[v-xjiin-msft]

Hi @dpombal,

The MAX() function in "&& Contracts[version] =  MAX(Contracts[version])" is returning the current row content not the MAX value. If you want to get the MAX version. You should do this:

MAX Version =
CALCULATE (
    MAX ( Contracts[version] ),
    FILTER (
        Contracts,
        Contracts[date_from] <= MAX ( 'Date'[Date] )
            && Contracts[date_to] > MAX ( 'Date'[Date] )
    )
)

Then change the SUM Debt formula to:

SUM Debt v3 = 
CALCULATE (
    [amount_total],
    FILTER (
        Contracts,
        Contracts[date_from] <= MAX ( 'Date'[Date] )
            && Contracts[date_to] >= MAX ( 'Date'[Date] )
			&& Contracts[version] =  [MAX Version]
    )
)

So basically, it is same as the formula in SUM Debt v2. It also needs to change >= to >. Since SUM Debt v2 can return your expected result, why do you need this SUM Debt v3?

Modified pbix file: https://1drv.ms/u/s!AlqSnZZUVHmshXPt462d8-w6pTJK

Thanks,
Xi Jin.

[dpombal]

q1:

Is MAX version measure operating at contract_id level,

Imagine contract_id 7 and contract_id 8 with version 2 and 3 changing the same day,

will this max  measure operate for each contract_id and calculate max_version ?

question2 :

So basically, it is same as the formula in SUM Debt v2. It also needs to change >= to >. Since SUM Debt v2 can return your expected result, why do you need this SUM Debt v3?

reply 2:

In regards to this, I am trying to reproduce another usecases, with more than 1 change per day

Just one final question:

Imagine my final correct measure is SUM Debt v2

Using the Date Calendar table, currently SUM Debt v2 works using Date day on chart axis, what i should need for future is.

If viewing data at a month level:

• If month level get the max of  all days in this month SUM Debt v2
• If month year level the max of  all 365 days in this year SUM Debt v2

Thanks in advance