Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

hiding YOY difference for future dates

Hi,   I'm trying to do a calculation of YOY actual difference.  I am able to not calculate future months but the total is not correct.  I think I need to use a SUMX instead but not sure how to writ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Does this measure work?

    Measure = SUMX(SUMMARIZE(Calendar,Calendar[Year],Calendar[Month],"ABCD",[diff YoY Acctual]),[ABCD])

    If it does not, then share the link from where i can download your PBI file.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello Ashish,

     

    in the dummy file I created a custom column called month name in the calendar table, then used the measure you created 

     

    Month number = MONTH('calendar'[calendar])
    Measure = SUMX(SUMMARIZE('calendar','calendar'[Year],'calendar'[Month Name],"ABCD",[diff yoy amount]),[ABCD])
     
    but I could not get the total to calculate correctly.  also, if I use the Month name and Year in the calendar table separatly (w/o date hiearchy) i can not longer sort on the year.  did you make any other changes to the file?  I'd like to understand what changes were made so I can implment the code to actual data.
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

Does this measure work?

Measure = SUMX(SUMMARIZE(Calendar,Calendar[Year],Calendar[Month],"ABCD",[diff YoY Acctual]),[ABCD])

If it does not, then share the link from where i can download your PBI file.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello Ashish, 

 

THanks for the reply, when I try to put the measure in, this is the error I get. I do have a calendar table.  how do you prefer I share the PBIX File?

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Upload the file on Google Drive and share the download link here.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      thanks I'm going to create some dummy data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Ashish,

       

      in the dummy file I created a custom column called month name in the calendar table, then used the measure you created 

       

      Month number = MONTH('calendar'[calendar])
      Measure = SUMX(SUMMARIZE('calendar','calendar'[Year],'calendar'[Month Name],"ABCD",[diff yoy amount]),[ABCD])
       
      but I could not get the total to calculate correctly.  also, if I use the Month name and Year in the calendar table separatly (w/o date hiearchy) i can not longer sort on the year.  did you make any other changes to the file?  I'd like to understand what changes were made so I can implment the code to actual data.
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much for your help, but now I have a different problem,

       

      As you can see the YOY is calculated correctly now at the year month level, but it is not correct at the customer level.  I need a generic YOY difference measure that can calculate YOY difference in by any column.  Any help with how to modify the code would be super helpful.  Thank you again for your help.

       

      https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing