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

[PBI_Publisher]

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

[amitchandak · Accepted solution]

@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-Differ...

[PBI_Publisher]

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.

[PBI_Publisher]

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. 

[PBI_Publisher]

Thanks , @amitchandak  It  worked for me.