Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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/

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/

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.