Year-over-Year Measure by Month

[k2koruni]

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)

[v-jianboli-msft · Accepted solution]

Hi @k2koruni ,

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

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:

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.

[k2koruni]

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

[Greg_Deckler]

@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...

[k2koruni]

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.