Calculate differences with reset every year

Question

Hi,&nbsp;I need some inputs on how to calculate the differences on a calculated running total matrix with a restart every year.&nbsp;First step was to calculate the running totals which I have completed with this measure:Vacancy, RT = //distinct count by RessourceIdCALCULATE(DISTINCTCOUNT(Resources[ResourceId]),Resources[Vacancy] = TRUE ,DATESYTD(Resources[EarliestAvailable].[Date]))Results in table A:&nbsp;Rolling Totals&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Year2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GROUPJanFebMarAprMayJunJulAugSepOctNovDecTOTALJan&nbsp;FebMarAprMayJunJulAugSepOctNovDecTOTALAA000000222555510101012121212-6-6-6-6-6-6BB222222222222212121212141415151515151515CC00000000000003333333444444TOTAL222222444777725252527292930131313131313&nbsp;As you can see the running calculation will restart every year as it should.&nbsp;Next step is to calculate the differences as "Vacancy, RT -&nbsp;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 bTable b- WRONG OUTPUT:SHOW DIFFERENCES WONG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Year2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GROUPJanFebMarAprMayJunJulAugSepOctNovDecTOTALJan&nbsp;FebMarAprMayJunJulAugSepOctNovDecTOTALAA00000020030055002000-180000-11BB2000000000002100002030000013CC00000000000003000000100004TOTAL200000200300718002203-1700006&nbsp;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.&nbsp;Table c- EXPECTED RESULT:SHOW DIFFERENCES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Year2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GROUPJanFebMarAprMayJunJulAugSepOctNovDecTOTALJan&nbsp;FebMarAprMayJunJulAugSepOctNovDecTOTALAA000000200300510002000-180000-6BB2000000000002120002010000015CC00000000000003000000100004TOTAL200000200300725002201-17000013&nbsp;Hope someone have some ideas on how to deal with this.&nbsp;&nbsp;

v-cazheng-msft · Accepted Answer

Hi&nbsp;Anonymous,
&nbsp;
You may create a Measure.
Separate Year= CALCULATE([Vacancy, HC (Increase)] ,FILTER(Resources, VALUES(Resources[EarliestAvailable].Year)))
&nbsp;
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!
&nbsp;
Best Regards,
Community Support Team _ Caiyun

Forum Discussion

Calculate differences with reset every year

1 Reply

Related content

Dynamic Calculation Different Period

Different Calculations

How to reset Eventhouse Ingestion from Batching to Streaming ?

Calculating Differences

Arc GIS Map will not reset when page filters are reset

Rolling Totals
Year	2021													2022
GROUP	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL
AA	0	0	0	0	0	0	2	2	2	5	5	5	5	10	10	10	12	12	12	12	-6	-6	-6	-6	-6	-6
BB	2	2	2	2	2	2	2	2	2	2	2	2	2	12	12	12	12	14	14	15	15	15	15	15	15	15
CC	0	0	0	0	0	0	0	0	0	0	0	0	0	3	3	3	3	3	3	3	4	4	4	4	4	4
TOTAL	2	2	2	2	2	2	4	4	4	7	7	7	7	25	25	25	27	29	29	30	13	13	13	13	13	13

SHOW DIFFERENCES WONG
Year	2021													2022
GROUP	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL
AA	0	0	0	0	0	0	2	0	0	3	0	0	5	5	0	0	2	0	0	0	-18	0	0	0	0	-11
BB	2	0	0	0	0	0	0	0	0	0	0	0	2	10	0	0	0	2	0	3	0	0	0	0	0	13
CC	0	0	0	0	0	0	0	0	0	0	0	0	0	3	0	0	0	0	0	0	1	0	0	0	0	4
TOTAL	2	0	0	0	0	0	2	0	0	3	0	0	7	18	0	0	2	2	0	3	-17	0	0	0	0	6

SHOW DIFFERENCES
Year	2021													2022
GROUP	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL
AA	0	0	0	0	0	0	2	0	0	3	0	0	5	10	0	0	2	0	0	0	-18	0	0	0	0	-6
BB	2	0	0	0	0	0	0	0	0	0	0	0	2	12	0	0	0	2	0	1	0	0	0	0	0	15
CC	0	0	0	0	0	0	0	0	0	0	0	0	0	3	0	0	0	0	0	0	1	0	0	0	0	4
TOTAL	2	0	0	0	0	0	2	0	0	3	0	0	7	25	0	0	2	2	0	1	-17	0	0	0	0	13