Calculate differences with reset every year

Anonymous — Thu, 09 Jun 2022 13:52:17 GMT

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

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

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

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

Re: Calculate differences with reset every year

v-cazheng-msft — Mon, 13 Jun 2022 05:17:40 GMT

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

topic Calculate differences with reset every year in DAX Commands and Tips

Calculate differences with reset every year

Re: Calculate differences with reset every year