Help with calculations based on slicer selection

[Mente73]

Hello everyone, new to Power Bi and forum.

I appeciate all your help in advance.

I am creating a matrix and want to include a slicer with week, month, Qtr and 1-2H (done through a prm)

In the matrix I have total active customers and total new buying customers and want to measure the retention rate (active customers of current period - FTD's from current period) / active customers from previous period.

When the selection in the slicer is week, I have no problems, it does the calculation correctly, however when changed to month,  year or QTR, I am getting a infinity calculation. I am assuming it is because the measue for retention only contemplates actives from previous week.

How can I go to make it calculate based on the period selected?

Calculation meassures:

Actives = DISTINCTCOUNT('Actives New'[CustomerID])

FTD's = DISTINCTCOUNT('FTD's'[CustomerID])

Actives PW = CALCULATE(DISTINCTCOUNT('Actives New'[CustomerID])

,FILTER(ALL(DimDate),

DimDate[Year]=SELECTEDVALUE(DimDate[Year]) &&

DimDate[WeekofYear]=SELECTEDVALUE(DimDate[WeekofYear])-1))

Retention % = ([Active]-[FTD's])/[Actives PW]

Attached a sample.  in image one its OK to have infinty for week 1.

Again, I appreciate all your help.

Thanks

[Mente73]

Sevenhills,

Thank you for your help.

Regards

M

[Anonymous]

Hi, @Mente73 

Thanks for sevenhills's reply. You can provide the pbix file without sensitive data for testing, it's hard to reproduce your data with just screenshots. Feel free to help you.

Best Regards,
Yang
Community Support Team

[sevenhills]

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.

Actives PW = CALCULATE(DISTINCTCOUNT('Actives New'[CustomerID])

,FILTER(ALL(DimDate),

DimDate[Year]=SELECTEDVALUE(DimDate[Year]) &&

DimDate[WeekofYear]=SELECTEDVALUE(DimDate[WeekofYear])-1))

You are getting value for SELECTEDVALUE(DimDate[Year]) from the slicer. I am not sure what values you used for the other slicer? can you clarify?

Also, could you you create a dummy measure and add to your table and see what value you are getting for "SELECTEDVALUE(DimDate[WeekofYear]"?

[Mente73]

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

[Mente73]

Hello Sevenhills.

Thank you for checking on it,

I am attaching a few more screenshot that may help you to answer your questions. Like I said, I an a newbie in PBI and get lost too. 

This is the DimDate I havethis is the prm parameteresThis is the other slicer (Year)This is what I get from dummy measure

Again, thank you for helping me out

[sevenhills]

You are using dynamic time calculations.

There need to be changes. See if these videos help ... if not, let me know, we can solve it.

Check these videos 

https://www.youtube.com/watch?v=MYHG-QSM8qw

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

          (Link to PBIX file is provided in the info)

[Mente73]

Watched both and the 2nd is closer to what I am trying to achieve however I got lost on the VAR for the periods as they are different to the one I need.

I appreciate if you can help me to make it work.  I can start from scratch.

Thanks

M

[sevenhills]

Share the .pbix file by removing the sensitive data. I agree with @Anonymous , it is hard for anyone to help!

Theory where you are getting infinity / failing the measue:

• You have parameters and the dynamic measure concept.
  The videos talk the same.
• Your Active and FTD measures are basic measures. No issues with these!
• Actives PW and Retention % are based on the parameter/slicer values, which is dynamic in your case.
• Retention % is good in DAX. Once you fix the Actives PW, then this will work.
• DimDate[Year]=SELECTEDVALUE(DimDate[Year]) .. this is from your Year Slicer. No issues!
• The issue is using of your slicer selection in this line. The videos talk these and also has .pbix file. You have to use SWITCH in DAX: DimDate[WeekofYear]=SELECTEDVALUE(DimDate[WeekofYear])-1))
  • Based on the slicer, the columns are switching and based on this, you have to change the DAX behavior. 

Hope it helps! 

[Mente73]

Hey guys, 

Sorry for the delay.

I have added a sample data @ https://github.com/Mente73/Files 

I apprecite any assitance 

M

[sevenhills · Accepted solution]

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!