cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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]))

3 ACCEPTED SOLUTIONS
Super User

Hi,

Does this measure work?

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.
13 REPLIES 13
Super User

Hi,

Does this measure work?

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

Helper II

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?

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.
Helper II

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.

Helper II

thanks I'm going to create some dummy data.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors