cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bo_wang5
Helper II
Helper II

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

 

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

bo_wang5_1-1628288718537.png

 

 

3 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

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?

 

bo_wang5_0-1628306091509.png

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

 

bo_wang5_0-1628356390069.png

 

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

View solution in original post

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.

thanks I'm going to create some dummy data.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.