aggregate chart

[hodobee]

Hi!

I have a problem I can't solve. I have a January aggregate chart and a February aggregate chart. If January and February are selected together in the filter it works fine. However, if I select only February, then the slices where there was no value for January are dropped. I have turned off the interaction between the filter and the chart and put a measure on the max date, but that way it counts with the max date, not February.
Or it doesn't work with that either:

calc_chart =
VAR max_month = Max('A'[date])
RETURN
CALCULATE(
DISTINCTCOUNT('A'[number]),
FILTER(
ALL('A'),
'A'[result] = "closed" &&
'A'[date] <= max_month &&
(ISBLANK(SELECTEDVALUE('A'[where])) || 'A'[where] = SELECTEDVALUE('A'[where]) &&
(ISBLANK(SELECTEDVALUE('A'[year

 

])) || 'A'[year] = SELECTEDVALUE('A'[year]))

)))
January:january

January+February
Just February, dropped 2 slice 😞february
Thanks

[Anonymous · Accepted solution]

Hi @hodobee ,

Based on the testing, creating the date table to filter the chart.

Dates = CALENDAR(MIN('A'[date]), MAX('A'[date]))

Then, try using the following DAX formula to calculate the aggregate based on the selected month.

calc_chart = 
   VAR max_month = MAX('Dates'[Date])
   RETURN
   CALCULATE(
       DISTINCTCOUNT('A'[Number]),
       FILTER(
           ALL('A'),
           'A'[Result] = "closed" &&
           'A'[Date] <= max_month &&
           (ISBLANK(SELECTEDVALUE('A'[where])) || 'A'[where] = SELECTEDVALUE('A'[where])) &&
           (ISBLANK(SELECTEDVALUE('A'[Year])) || 'A'[Year] = SELECTEDVALUE('A'[Year]))
       )
   )

Drag the date table month column to the slicer visual.

Select the month. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.