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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate differences with reset every year

Hi, 

I need some inputs on how to calculate the differences on a calculated running total matrix with a restart every year. 

First step was to calculate the running totals which I have completed with this measure:

Vacancy, RT = //distinct count by RessourceId
CALCULATE(
DISTINCTCOUNT(Resources[ResourceId]),Resources[Vacancy] = TRUE ,
DATESYTD(Resources[EarliestAvailable].[Date]))

Results in table A: 

Rolling Totals                        
Year2021            2022            
GROUPJanFebMarAprMayJunJulAugSepOctNovDecTOTALJan FebMarAprMayJunJulAugSepOctNovDecTOTAL
AA000000222555510101012121212-6-6-6-6-6-6
BB222222222222212121212141415151515151515
CC00000000000003333333444444
TOTAL222222444777725252527292930131313131313

 

As you can see the running calculation will restart every year as it should. 

Next step is to calculate the differences as "Vacancy, RT - Vacancy, RT previous month"

I have tried to calculate "Vacancy, RT previous month" with the following measure:

Vacancy, Previous Mo =
CALCULATE(
[Vacancy, RT],
DATESYTD( PREVIOUSMONTH(Resources[EarliestAvailable].[Date]))
)

After that I have simply made the folloowing formel for calculating the differences:
Vacancy, HC (Increase) =
CALCULATE([Vacancy, RT]-[Vacancy, Previous Mo],DATESYTD(Resources[EarliestAvailable].[Date]))


Problem is this will give the wrong output as shown in Table b
Table b- WRONG OUTPUT:
SHOW DIFFERENCES WONG                      
Year2021            2022            
GROUPJanFebMarAprMayJunJulAugSepOctNovDecTOTALJan FebMarAprMayJunJulAugSepOctNovDecTOTAL
AA00000020030055002000-180000-11
BB2000000000002100002030000013
CC00000000000003000000100004
TOTAL200000200300718002203-1700006

 

As shown in the table it will substract the total from 2021 on january 2022. In fact I want the years to be seperated as shown in Table c. Every January it should start from it actual values. 

Table c- EXPECTED RESULT:

SHOW DIFFERENCES                       
Year2021            2022            
GROUPJanFebMarAprMayJunJulAugSepOctNovDecTOTALJan FebMarAprMayJunJulAugSepOctNovDecTOTAL
AA000000200300510002000-180000-6
BB2000000000002120002010000015
CC00000000000003000000100004
TOTAL200000200300725002201-17000013

 

Hope someone have some ideas on how to deal with this. 


 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @Anonymous,

 

You may create a Measure.

Separate Year= CALCULATE([Vacancy, HC (Increase)] ,FILTER(Resources, VALUES(Resources[EarliestAvailable].Year)))

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

View solution in original post

1 REPLY 1
v-cazheng-msft
Community Support
Community Support

Hi @Anonymous,

 

You may create a Measure.

Separate Year= CALCULATE([Vacancy, HC (Increase)] ,FILTER(Resources, VALUES(Resources[EarliestAvailable].Year)))

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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