hiding YOY difference for future dates

[bo_wang5]

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 write the logic.  any help would be appriciated.

diff YOY Acctual = if(isblank([Pipeline]),"",([Pipeline]-[Pipeline ly]))

[Ashish_Mathur · Accepted solution]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

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.

[bo_wang5]

tried this measure with the dummy data, did not work:-(

[bo_wang5]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[bo_wang5]

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

[Ashish_Mathur]

Hi,

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

[bo_wang5]

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

[Ashish_Mathur]

You are welcome.

[bo_wang5]

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

[Ashish_Mathur]

What problems are you facing?  Describe the question clearly and also show the expected result.

[bo_wang5 · Accepted solution]

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.

[bo_wang5]

https://drive.google.com/file/d/1DUe9RNOXMqIOPGB1n4h-197L0Yni9f9c/view?usp=sharing

here you go, I created the file with diff YOY amount just calcuated as revenue-revnue ly.  as you can see it is calculating into the future, I'd like to exclude that from my visual.

[bo_wang5]

thanks I'm going to create some dummy data.