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.
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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you Ashish, I got it!! thanks for your help.

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

      You are welcome.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I'm still having alot of trouble actualy with sums, can you help?