Forum Discussion

hodobee's avatar
hodobee
New Member
1 year ago
Solved

aggregate chart

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's avatar
    Anonymous
    1 year ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.