topic Re: Getting Max Date based on slicer selection and Use That to get difference between 2 dates.. in DAX Commands and Tips

Getting Max Date based on slicer selection and Use That to get difference between 2 dates..

PBI_Publisher — Thu, 09 Jul 2020 13:22:38 GMT

Hi DAX Experts,

I'm kind of struggling to achieve a result using dax.

Formula's used to achieve:

MaxDate = var maxselected=CALCULATE(MAX('Date'[Date]))return if(ISFILTERED('Date'[Year]),maxselected)
Ageing is := DATEDIFF(Sheet1[ShortCreatedDate],[MaxDate],DAY)
AverageAge: AvgAge = AVERAGE(Sheet1[ageing])

Requirement it to calculate the average age of ticket, but based on slicer selection.

1.If a year is seleted the ageing should be calculated as difference of created date and the last date available month of the selected year.
2.If a year & Quarter is seleted the ageing should be calculated as difference of created date and the last date available month of the selected quarter for that particular year.
3.If a year & Quarter & Month is seleted the ageing should be calculated as difference of created date and the last date available month of the selected month for that particular year & Quarter.

As part of my Trial to achieve the same i have used max date measure which i feel is giving the last date based on th selected slicer.

The part where i'm getting confused is when i use the measure to find the ageing I'm getting Blank as value.

Can someone please point me in the right direction to get the desired value.

Have also attached the screen shot & PBIX file in the below link.

https://drive.google.com/file/d/1quH8cDAUpESthO6qx09Bji6whWn6M7fq/view?usp=sharing

Re: Getting Max Date based on slicer selection and Use That to get difference between 2 dates..

amitchandak — Thu, 09 Jul 2020 16:54:56 GMT

PBI_Publisher , As long as a month, qtr are selected from date table it will be taken care.

You need to take care of row context

measure =
var _max = MAXX(allselected('Date'),'Date'[Date])

return DATEDIFF(max(Sheet1[ShortCreatedDate]),_max,DAY)

for row context refer

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

Re: Getting Max Date based on slicer selection and Use That to get difference between 2 dates..

PBI_Publisher — Fri, 10 Jul 2020 08:39:20 GMT

Thanks , amitchandak It worked for me.

Re: Getting Max Date based on slicer selection and Use That to get difference between 2 dates..

PBI_Publisher — Tue, 14 Jul 2020 12:58:12 GMT

Hi amitchandak ,

The last idea which u suggested worked for me. Adding to that i have a requirement where after finding the difference of between the dates. I need to put the value into a calculated colum/Measure where it will be grouped in the below format based on the days difference of 2 dates i need to make a bucket. (0-3 days, 4-7 days... like this).

Can You please help me in achieveing this.

Re: Getting Max Date based on slicer selection and Use That to get difference between 2 dates..

PBI_Publisher — Wed, 15 Jul 2020 05:36:40 GMT

I think i'm missing something silly here i tried achieveing it but it always shows me difference from current day not from the selected month last day. What could be the problem here? Can someone please guide me.

Thnaks in advance.