Search code examples
powerbidax

DAX Calculate Not Returning Expected Results Based On Date Filter

I have the following measure to calculate case sales in the last 12 months from the selected slicer end date. I would expect it to filter sales based on the provided start and end date variables included in the filter, however when looking at the dates included in the sales graph it includes start dates well past the last 12 months. I have included cards below the graph which show the start and end date calculations.

Cases Total Current 12mo (Slicer) = 
VAR EndDate =
    DATE ( YEAR ( MAX( 'Date'[Date] ) ), MONTH ( MAX ( 'Date'[Date] ) ), 1 )
VAR StartDate =
    EDATE ( EndDate, -11 )
RETURN
    CALCULATE (
        SUM ( Sales[Cases] ),
        FILTER ( 'Date', 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate )
    )

enter image description here

I am at a loss as to why the start and end dates are correct in the included cards, but those same dates are not applying to the start date of the calculation filter.

Any help would be greatly appreciated!

Solution

  • Based on your formula:

    Cases Total Current 12mo (Slicer) = 
VAR EndDate =
    DATE ( YEAR ( MAX( 'Date'[Date] ) ), MONTH ( MAX ( 'Date'[Date] ) ), 1 )
VAR StartDate =
    EDATE ( EndDate, -11 )
RETURN
    CALCULATE (
        SUM ( Sales[Cases] ),
        FILTER ( 'Date', 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate )
    )

    The calculation is evaluated for each bar in the bar chart. For instance, for May 2023, we can assume the following:

    • MAX ( 'Date'[Date] ) will evaluate to 2023-05-31
    • EndDate will evaluate to the 1st date in the maximum year/month in the evaluation context
      • This will be 2023-05-01
    • StartDate will evaluate to a date 11 months prior to EndDate
      • This will be 2022-06-01
    • The SUM over Sales[Cases] will then aggregate rows in this time period