Forum Discussion
Help with calculations based on slicer selection
- 1 year ago
Check this video for similar solution:
https://www.youtube.com/watch?v=obfYqap4hnQ
Coming back to your .pbix, I quickly fix and it is very slow. You may have to do as per the video or look on internet for better performance.
Optional measure for debug:Measure debug = var _sv = Max( Prm_Date[Prm_Date] ) RETURN _sv & " : " & switch ( _sv, "Date", SELECTEDVALUE(DimDate[Date]), "Week", SELECTEDVALUE(DimDate[WeekofYear]), "Month", SELECTEDVALUE(DimDate[Month]), "Quarter", SELECTEDVALUE(DimDate[Quarter]), "Half Year", SELECTEDVALUE(DimDate[Half Year]) , "??")Actives PW = var _sv = Max( Prm_Date[Prm_Date] ) RETURN CALCULATE(DISTINCTCOUNT('Actives New'[CustomerID]) ,FILTER(ALL(DimDate), DimDate[Year]=SELECTEDVALUE(DimDate[Year]) && switch ( _sv, "Date", DimDate[Date] = SELECTEDVALUE(DimDate[Date]), "Week", DimDate[WeekofYear]=(SELECTEDVALUE(DimDate[WeekofYear])-1) , "Month", DimDate[Month] = SELECTEDVALUE(DimDate[Month]), "Quarter", DimDate[Quarter] = SELECTEDVALUE(DimDate[Quarter]), "Half Year", DimDate[Half Year]= SELECTEDVALUE(DimDate[Half Year]) , "??") ) )Retention % = If ( not ISBLANK([Actives PW]) && [Actives PW] <> 0, ([Active]-[FTD's])/[Actives PW] )I just added Measure debug and Actives PW for checking. you can remove those in the below visuals:
sample for half year:
sample for quarter:
Hope it helps!
First, you can get rid of infinity using
Retention % =
IFERROR(([Active]-[FTD's])/[Actives PW], BLANK() )
Second, your formula for getting the Actives PW uses WeekofYear and when you select other than the week, it obviously not work correctly and which in turn results to infinity.
Watched both videos and 2nd relates more however i got lost on the VAR for the periods since they differ from mine.
If you think you can help me out, I can start from scratch to make it happen
Regards
M