Comparing rolling years....?

[GWTF]

Hi all,

Yeah, even a good subject decription was hard...

I'm running into the boundaries of my limited Power BI capabilities 😁 AKA: I need help!!

I'm trying to create a measure that gives me the possibility to compare the total amount of registered incidents in a "running" 1-year period. In a way that by selecting a random date (let's say the 12th of May 2022) the total of that running year (values ranging from the 12th of May 2021 till the 12th of May 2022) are compared with the totals of previous running years (12-05-2020 to 12-05-2021 AND 12-05-2019 to 12-05-2020, etc.).

I cannot wrap my head around it.

I have a date-table (in Dutch):

Datum = ADDCOLUMNS ( CALENDAR (DATE(2018, 1, 1), DATE(2039,12,31)),

"DatumInteger", FORMAT ([Date], "YYYYMMDD" ),

"Jaar", YEAR ([Date]),

"Kwartaal", QUARTER ([Date]),

"Maandnummer", FORMAT ([Date], "MM" ),

"JaarMaandNummer", FORMAT ([Date], "YYYY/MM" ),

"JaarMaandkort", FORMAT ([Date], "YYYY/mmm" ),

"Maandnaamkort", FORMAT ([Date], "mmm" ),

"Maandnaamlang", FORMAT ([Date], "mmmm" ))

And a table with incidents:

Incident_date	Year	Month	Incident_ID
17-3-2018	2018	3	U61878
18-4-2018	2018	4	U62535
23-7-2018	2018	7	U64294
1-8-2018	2018	8	U64476
4-8-2018	2018	8	U64537
8-8-2018	2018	8	U64609
17-8-2018	2018	8	U65010
20-8-2018	2018	8	U65013
23-8-2018	2018	8	U65090
7-9-2018	2018	9	U86699
13-9-2018	2018	9	U86763
9-4-2019	2019	4	U123774
19-4-2019	2019	4	U123953
24-4-2019	2019	4	U123988
25-4-2019	2019	4	U124015
14-5-2019	2019	5	U124301
1-7-2019	2019	7	U124981
14-8-2019	2019	8	U125771
10-2-2020	2020	2	U128336
24-2-2020	2020	2	U128572
6-3-2020	2020	3	U128808
9-3-2020	2020	3	U128842
29-6-2020	2020	6	U130464
24-9-2020	2020	9	U131509
6-10-2020	2020	10	U131626
6-3-2021	2021	3	U133552
16-12-2021	2021	12	31280
16-12-2021	2021	12	31637
20-12-2021	2021	12	33113
17-1-2022	2022	1	37908
24-1-2022	2022	1	39580
3-2-2022	2022	2	42963
7-2-2022	2022	2	43327
1-3-2022	2022	3	38947
4-3-2022	2022	3	49552
22-3-2022	2022	3	50820
25-3-2022	2022	3	53646
30-3-2022	2022	3	54375
5-4-2022	2022	4	55017

Any assistance would be highly appreciated!

Kind regards,

Wilco

[Anonymous · Accepted solution]

Hi @GWTF ,

You could refer below formulas.

_YTD =
CALCULATE (
    SUM ( Incidents_table[amount] ),
    FILTER (
        ALLSELECTED ( Incidents_table ),
        Incidents_table[Incident_date]
            > EDATE ( SELECTEDVALUE ( Incidents_table[Incident_date] ), -12 )
            && Incidents_table[Incident_date]
                <= SELECTEDVALUE ( Incidents_table[Incident_date] )
    )
)

P_YTD =
CALCULATE (
    SUM ( Incidents_table[amount] ),
    FILTER (
        ALLSELECTED ( Incidents_table ),
        Incidents_table[Incident_date]
            > EDATE ( SELECTEDVALUE ( Incidents_table[Incident_date] ), -24 )
            && Incidents_table[Incident_date]
                <= EDATE ( SELECTEDVALUE ( Incidents_table[Incident_date] ), -12 )
    )
)

Best Regards,

Jay