DAX - Measure is wrong when using Date for analyze Calculate and DATESINPERIOD

[Vanchy_Liao]

Hi Everyone,

    I create two measures:

    1. SUM_QTY:

        (1) Goal: To sum all QTY in the data source

        (2) DAX: SUM(Sheet1[QTY])

    2. SUM_QTY_Last12M: 

        (1) Goal: To sum the QTY in the Last 12 Months

        (2) DAX: CALCULATE([SUM_QTY], DATESINPERIOD('Dim_MTH'[MTH], DATE(YEAR(NOW()), MONTH(NOW()), 01), -1, YEAR))

    The first one is working well, but the last one is weird.

    Why SUM_QTY_Last12M always shows 431? Although 431 is the correct anwser...

Month	SUM_QTY	SUM_QTY_Last12M	What I Expect
2021-01-01	98	431
2021-02-01	4	431
2021-03-01	33	431	33
2021-04-01	69	431	69
2021-05-01	81	431	81
2021-06-01	33	431	33
2021-07-01	6	431	6
2021-08-01	51	431	51
2021-09-01	33	431	33
2021-10-01	9	431	9
2021-11-01	21	431	21
2021-12-01	33	431	33
2022-01-01	6	431	6
2022-02-01	51	431	51
2022-03-01	38	431	38
Total	566	431	431

[VahidDM · Accepted solution]

Hi @Vanchy_Liao 

Try this:

SUM_QTY_Last12 = 
VAR _A =
    CALCULATE(
        [SUM_QTY],
        DATESINPERIOD( Sheet1[Month], DATE( 2022, 3, 1 ), -12, MONTH )
    )
VAR _B =
    CALCULATE(
        [SUM_QTY],
        FILTER(
            Sheet1,
            Sheet1[Month]
                IN DATESINPERIOD( Sheet1[Month], DATE( 2022, 3, 1 ), -12, MONTH )
        )
    )
RETURN
    IF( ISFILTERED( Sheet1[Month] ), _B, _A )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[amitchandak]

@Vanchy_Liao , to me last column seem same as the first one

for rolling

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You can change no of months as per need

Abstract Thesis Part 60: Rolling Months Formula: https://youtu.be/GS5O4G81fww