cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpombal Member
Member

DAX SCD Get amount between date from and date to

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)

 

SCD1.PNG

 

 

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)

 

 

SCD2.PNGsdas

 

 

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)

 

SCD3.PNG

 

 

 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

 

8 REPLIES 8
v-xjiin-msft Super Contributor
Super Contributor

Re: DAX SCD Get amount between date from and date to

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 Member
Member

Re: DAX SCD Get amount between date from and date to

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

Highlighted
v-xjiin-msft Super Contributor
Super Contributor

Re: DAX SCD Get amount between date from and date to

Hi @dpombal,

 

Sorry. Check this:

 

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

 

Thanks,
Xi Jin.

dpombal Member
Member

Re: DAX SCD Get amount between date from and date to

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 Super Contributor
Super Contributor

Re: DAX SCD Get amount between date from and date to

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.

dpombal Member
Member

Re: DAX SCD Get amount between date from and date to

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.

SCD4.PNG

 

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 Super Contributor
Super Contributor

Re: DAX SCD Get amount between date from and date to

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]
    )
)

 

33.PNG

 

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 Member
Member

Re: DAX SCD Get amount between date from and date to

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,089)