Calculate 12/24 Month Difference from a Quarter

[BjoernSchaefer]

Hi,

I am facing a challenge: I have a table "fct_BASE" with the columns FAZID and DATE_PERFORMANCE. I need 2 measures that give me the count of FAZID from the end of the quarter. It is linked to a Calendar and another dimesnsion Table

The tabel ist structured as follows:

FAZID	DATE_PERFORMANCE
SYS0002910	18.07.2023
SYS0002910	17.08.2021
SYS0002910	25.10.2022
SYS0002920	04.05.2023
SYS0002920	09.12.2021
SYS0002920	15.10.2020
SYS0002920	20.07.2021
SYS0002920	20.07.2021
SYS0002920	20.07.2021

That is the Outcome of the two Measures underneath the table:

Year	Quarter	12 Mon	24 Mon
2023	1	33351	33351
2023	2	28448	28448

These are the measures:

12_Mon:=
VAR Quarter_End = MAX(dim_Calendar[Date])
RETURN
CALCULATE (
COUNT ( fct_Base[FAZID] ),
ALL(fct_Base[DATE_PERFORMANCE]),
fct_Base[DATE_PERFORMANCE] >= Quarter_End - 365,
fct_Base[DATE_PERFORMANCE] <= Quarter_End
)

24_Mon:
=VAR Quarter_End = MAX(dim_Calendar[Date])
RETURN
CALCULATE (
COUNT ( fct_Base[FAZID] ),
ALL(fct_Base[DATE_PERFORMANCE]),
fct_Base[DATE_PERFORMANCE] >= Quarter_End - 730,
fct_Base[DATE_PERFORMANCE] <= Quarter_End
)

I don't understand why the values in the columns are identical.
Does anybody has a clue?
Thanks a thousand times in advance.

Regards

Björn

[JoeBarry · Accepted solution]

Hi @BjoernSchaefer 

Please try this. 

1st measure

Total Fazid = Count(fct_base[FAZID])

2nd measure using the measure previously

last 12 Months = CALCULATE (
    [Total Fazid],
    DATESINPERIOD ( 'DIM Date'[Date], MAX ( 'DIM Date'[Date] ), -12, MONTH )

3rd

last 24 Months = CALCULATE (
    [Total Fazid],
    DATESINPERIOD ( 'DIM Date'[Date], MAX ( 'DIM Date'[Date] ), -24, MONTH )

Thanks

Joe

[BjoernSchaefer]

Hey @JoeBarry ,

Thank you very much. I couldn't see the forest for the trees.
Regards

Björn