Creating Ageing Bucket Based on slicer Selection

[PBI_Publisher]

Hi All,

Good Day

I am trying to achieve a calculate column which will have the ageing of a ticket, 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.

I have achieved this part with help of below measure(Selected Max Date) works fine.But when i use that to put on bucketing it always calculates the difference from the current date. Which i don't need . It should always ccalculated the differecne from selected slicer and has to be kept on bucketing.

Can someone please help me solving this.

Measure

----------

SelectedMaxDate:= MAXX(allselected(Date_Table),Date_Table[ShortDate])

CalculatedColumns

-------------------------

Bucketing:=MAXX(VALUES(F_Master_Stg_Data[Number]),CALCULATE(DATEDIFF(MAX(F_Master_Stg_Data[Short_Created_Date]),[SelectedMaxDate],DAY),ALL(Date_Table)))

AgeingBucket:==IF(F_Master_Stg_Data[Bucketing]<=3,"0-3",IF(F_Master_Stg_Data[Bucketing]<=7,"4-7",IF(F_Master_Stg_Data[Bucketing]<=14,"8-14",IF(F_Master_Stg_Data[Bucketing]<=24,"15-24",IF(F_Master_Stg_Data[Bucketing]<=30,"25-30",IF(F_Master_Stg_Data[Bucketing]<=35,"31-35",IF(F_Master_Stg_Data[Bucketing]<=40,"36-40",">40")))))))

Bucketing is the value which i'm getting through DAX where the actual Bucket should be the last highlighted columns.

Bucketing is the value which i'm getting through DAX where the actual Bucket should be the last highlighted columns.

[harshnathani]

Hi @PBI_Publisher ,

Have a look at these videos

https://www.youtube.com/watch?v=3bV9BfB3_Oc

https://www.youtube.com/watch?v=iH3gW_vy69o

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[amitchandak]

@PBI_Publisher , refer if these can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization