DAX: Difference between MAX date selected in slicer and transaction date

[Anonymous]

Hi folks,

My aim is to calculate a column that gives the difference of days between max date selected in the date slicer and the dates present in transaction table.

I have 2 tables - DateMaster and TransactionTable. The date columns are DateID and PostingDate respectively which are related with one to many relationship.

Download Sample data .

Eg: If "To date" in the date slicer is 15-03-2019, then the calculated column should return difference between 15-03-2019 and 31-03-2015 (refer the sample data sheet) in the first row and so on.

Please help with some formulae.

Already tried the following -

Measure: MaxDate = CALCULATE(MAX(DateMaster[DateID]), ALLSELECTED(DateMaster[DateID]))

Calculated column: DaysDynamic = DATEDIFF(TransactionTable[PostingDate], [MaxDate], DAY)

 This gives all the values as 0 as MaxDate at row level will become the same as PostingDate.

Thanks in advance,

Suguna.

[v-xicai]

Hi @Anonymous ,

You can create measure DaysDynamic, and then delate the relationship between DateMaster and TransactionTable.

DaysDynamic = DATEDIFF([MaxDate],SELECTEDVALUE(TransactionTable[PostingDate]),DAY)

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeFsxv8QyiBJhQhQJyN0eYwBwRdG490OmHQo_i5lTbd5Ug?e=qIbbrw

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai 

Thanks for your reply.
The slicer that I am using in my case is date range slicer. In that case, I believe SELECTEDVALUE will not work as it will return more than one value.

Kind regards,

Suguna.

[Peejz_Jalmasco]

Hi @Anonymous

I've used the measure below to get the date difference:

Grace period test =

VAR MaxCalendarDate = MAX('CALENDAR'[Date])

VAR MaxLoanReleaseDate = MAX('P3 PN MASTER DATA'[LOAN RELEASE DATE])

RETURN

DATEDIFF(MaxLoanReleaseDate,MaxCalendarDate,MONTH)

Here is the result: