Search code examples
powerbidax

Issue when trying cumulate measure over months (starting from 0) at start of period using between date slicer

I have a measure which counts occurrences of things (registrations in this case). When charted on a simple column chart looks like this - this is using a between dates slicer.

enter image description here

This works as expected, I add a measure to cumulate all the registrations and chart it

var result = CALCULATE(
[cnt_cp_dereg_2yr_pls],
FILTER(
    ALL(DimDate),
     DimDate[DateKey] >= DATE(2023,4,1) &&  DimDate[DateKey]  <= MAX( DimDate[DateKey] )
)

)

this charted works as expected enter image description here

However when I try and derive the date from the min date on the slicer (MIN(DimDate[DateKey]) and simply replace the numbers in the DATE() function -

var result = CALCULATE(
[cnt_cp_dereg_2yr_pls],
FILTER(
    ALL(DimDate),
     DimDate[DateKey] >= DATE(YEAR(Min(DimDate[DateKey])), MONTH(Min(DimDate[DateKey])), DAY(Min(DimDate[DateKey]))) &&  DimDate[DateKey]  <= MAX( DimDate[DateKey] )
)

)

The chart reverts back to the original! This behaviour seems odd and im probably missing something obvious.

Solution

  • The min and max calculated will be the same as for the increment in the chart x-axis. You will need to use ALLSELECTED(...) to get the overall MIN on the chart (ie the slicer min).

    var minFilteredDate =
  CALCULATE(
    MIN(DimDate[DateKey]),
    ALLSELECTED(DimDate)
  )

var result =
  CALCULATE(
    [cnt_cp_dereg_2yr_pls],
    FILTER(
      ALL(DimDate),
      DimDate[DateKey] >= minFilteredDate &&
      DimDate[DateKey]  <= MAX( DimDate[DateKey]
    )
  )