Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to create a calculation group for YoY calculation( 2 measures without using sameperiodlastyear )

Hi Community, 

 

I have 2 measures called TY placed sales and LY placed sales created using base columns TY sales and LY sales. 

I created a measure like below and it is working as expected. 

YoY % (Sales) =
Var a = [TY Placed Sales]
Var b = [LY Placed Sales]
return
IF(ISBLANK(a) || a <=0 || b<=0 , BLANK() , DIVIDE(a-b,b))
 
However, there are lot of KPIs which requires same Yoy calculations. So , instead of creating a seperate measure , i tried creating a calculation group as shown below. 
 
YoY % (BOPIS Sales ) =
Var a = [TY Placed Sales]
Var b = [LY Placed Sales]
return
IF(ISBLANK(a) || a <=0 || b<=0 , BLANK() , DIVIDE(a-b,b))
 
This is not working as expected and not giving any results. 
kindly assist. 
 
 
Need Help
  • Deku's avatar
    Deku
    1 year ago

    You need to pass in the measure to consider using selectedmeasure(), so need something like

     

    Var maxDt = max( date[date] )
Var thisYear= Year( maxDt )
Var a = 
   Calculate(
      Selectedmeasure(),
      Datesbetween( dates[Date], date( thisYear, 1, 1 ), maxDt )
   )
Var b = 
   Calculate(
      Selectedmeasure(),
      DATEADD(Datesbetween( dates[Date], date( thisYear, 1, 1 ), maxDt ), -12, month)
   )
return
IF(ISBLANK(a) || a <=0 || b<=0 , BLANK() , DIVIDE(a-b,b))

6 Replies

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    You need to pass in the measure to consider using selectedmeasure(), so need something like

     

    Var maxDt = max( date[date] )
Var thisYear= Year( maxDt )
Var a = 
   Calculate(
      Selectedmeasure(),
      Datesbetween( dates[Date], date( thisYear, 1, 1 ), maxDt )
   )
Var b = 
   Calculate(
      Selectedmeasure(),
      DATEADD(Datesbetween( dates[Date], date( thisYear, 1, 1 ), maxDt ), -12, month)
   )
return
IF(ISBLANK(a) || a <=0 || b<=0 , BLANK() , DIVIDE(a-b,b))
  • Gokul_G16's avatar
    Gokul_G16
    Icon for Resolver I rankResolver I
    1 year ago

    Hi Anonymous ,

    Please try the following code,

    YoY = VAR CurrentYear = YEAR(SELECTEDVALUE('Date'[Date])) VAR PreviousYear = CurrentYear - 1 RETURN CALCULATE( SELECTEDMEASURE(), FILTER( ALL('Date'), 'Date'[Year] = PreviousYear ) )

    For YoY%, use this DAX expression: YoY% = VAR CurrentYear = YEAR(SELECTEDVALUE('Date'[Date])) VAR PreviousYear = CurrentYear - 1 VAR CurrentValue = SELECTEDMEASURE() VAR PreviousValue = CALCULATE( SELECTEDMEASURE(), FILTER( ALL('Date'), 'Date'[Year] = PreviousYear ) ) RETURN DIVIDE(CurrentValue - PreviousValue, PreviousValue, 0)

    Regards,
    Gokul,

    If you good with the answer do like me and share your thougths if you not find well...

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

     

    Yes you can create a calculation group in Tabular Editor to apply YoY logic across multiple measures like Sales and Margin. Could you please try below steps

    1. In Tabular Editor, right-click on Tables → Create New Calculation Group

    2. Add the following items:

     

    Current

    SELECTEDMEASURE()

     

    YoY

    VAR Current = SELECTEDMEASURE()
VAR LastYear = CALCULATE(Current, SAMEPERIODLASTYEAR('Date'[Date]))
RETURN Current - LastYear

     

    YoY % (optional)

    VAR Current = SELECTEDMEASURE()
VAR LastYear = CALCULATE(Current, SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE(Current - LastYear, LastYear)

     

    Make sure your 'Date' table is marked as a proper date table and connected to your fact table. Once set up, the calculation group will apply these logic items dynamically to any measure.

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Anonymous,

     

    Has your issue been resolved? If the solution provided by the community member effectively addressed your concern, kindly confirm.

    We encourage you to mark the response as Accepted Answer and provide Kudos if you found it helpful. This helps us ensure that valuable solutions are shared with the entire community.

    Should you have any further questions, please don't hesitate to reach out.

     

    Thank you for your understanding.

    • v-sgandrathi's avatar
      v-sgandrathi
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Anonymous,

       

      May I ask if you have gotten this issue resolved?

      If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

      Thank you.

       

      • v-sgandrathi's avatar
        v-sgandrathi
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi Anonymous,

         

        I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

         

        Thank you.