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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
k2koruni
New Member

Year-over-Year Measure by Month

I am working on YoY with MoM Growth change. So far I was able to calculate November 2022 - November 2021 change. However, for my December 2022 is showing a negative value since the table is automatically calculating December 2022 - December 2021 change. How do I write an IF statement to return a value of zero if I do not have any values for December ? 

Below is my formula that works. But, after the "RETURN" the IF Statement is semi wrong as I cannot figure out how to incorporate that if December 2022 is zero, then return value of zero. 

Any help is greatly appreciated.

 

MoM Premium Growth =
VAR YoYMoMPremium = Calculate ([Total Premium],dateadd(dates[Date],-12,Month))
RETURN
[Total Premium]-YoYMoMPremium
The "[Total Premium]-YoYMoMPremium" will display calculation change for December 2022-December 2021 even if I have no data for December 2022. 
 
I tried below but this is the wrong approach as prior month that have true negative value that I want to see are now changed to zero. All I want is that if there is no data for the month, give me zero. 
if(([Total Premium]-YoYMoMPremium)<0,0,[Total Premium]-YoYMoMPremium)
1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @k2koruni ,

 

Based on your description, I have created a simple sample:

vjianbolimsft_0-1672380767262.png

Please try:

MoM Premium Growth = 
VAR YoYMoMPremium = Calculate ([Total Premium],dateadd(dates[Date],-12,Month))
RETURN
IF(ISBLANK([Total Premium]),0,[Total Premium]-YoYMoMPremium)

Final output:

vjianbolimsft_1-1672380818387.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

Hi @k2koruni ,

 

Based on your description, I have created a simple sample:

vjianbolimsft_0-1672380767262.png

Please try:

MoM Premium Growth = 
VAR YoYMoMPremium = Calculate ([Total Premium],dateadd(dates[Date],-12,Month))
RETURN
IF(ISBLANK([Total Premium]),0,[Total Premium]-YoYMoMPremium)

Final output:

vjianbolimsft_1-1672380818387.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for all your help Jianbo Li. I appreciate it.

Greg_Deckler
Super User
Super User

@k2koruni You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg. I like the links you have provided as I will have use of them very soon. These are indeed very well put together. I appreciate the time and help you have provided. 

Thank you.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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