Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dpombal
Post Patron
Post Patron

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

 

9 REPLIES 9
v-xjiin-msft
Solution Sage
Solution Sage

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.

 

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

Hi @dpombal,

 

Sorry. Check this:

 

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

 

Thanks,
Xi Jin.

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

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.

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

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

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.