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

Question

Hi Everyone,&nbsp;&nbsp; &nbsp; I create two measures:&nbsp; &nbsp; 1. SUM_QTY:&nbsp; &nbsp; &nbsp; &nbsp; (1) Goal:&nbsp;To sum all QTY in the data source&nbsp; &nbsp; &nbsp; &nbsp; (2) DAX:&nbsp;SUM(Sheet1[QTY])&nbsp;&nbsp; &nbsp; 2. SUM_QTY_Last12M:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; (1) Goal:&nbsp;To sum the QTY in the Last 12 Months&nbsp; &nbsp; &nbsp; &nbsp; (2) DAX:&nbsp;CALCULATE([SUM_QTY], DATESINPERIOD('Dim_MTH'[MTH], DATE(YEAR(NOW()), MONTH(NOW()), 01), -1, YEAR))&nbsp;&nbsp; &nbsp; The first one is working well, but the last one is weird.&nbsp; &nbsp; Why&nbsp;SUM_QTY_Last12M always shows 431? Although 431 is the correct anwser...MonthSUM_QTYSUM_QTY_Last12MWhat I Expect2021-01-0198431&nbsp;2021-02-014431&nbsp;2021-03-0133431332021-04-0169431692021-05-0181431812021-06-0133431332021-07-01643162021-08-0151431512021-09-0133431332021-10-01943192021-11-0121431212021-12-0133431332022-01-01643162022-02-0151431512022-03-013843138Total566431431&nbsp;&nbsp;&nbsp;

vahiddm · Accepted Answer

Hi&nbsp;Vanchy_Liao&nbsp;
&nbsp;
Try this:
&nbsp;
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 )

&nbsp;
Output:
&nbsp;

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

Forum Discussion

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

2 Replies

Related content

Matrix Row Filtering Alters Rolling DATESINPERIOD Measures

DAX Calculated Measure

Datesinperiod Measure

DAX Calculate datesinperiod and filter

DAX Measure for Ranking the valid rows and calculating the average rating

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