Cumulative chart with filter

[RichardMo]

Beginner level. I have a table with columns including ‘week_start_date’, ‘region’, and 'appointment_count'.

I have a measure for a chart showing cumulative appointment_count over time, which works fine:

Cumulative measure = CALCULATE(

SUM(Table1[appointment_count]),

FILTER(ALL(Table1),

Table1[week_start_date]<=MAX(Table1[week_start_date])

))

I want to filter the cumulative chart by the ‘region’ column, but it seems you can’t filter the above code by anything except the week_start column, so I tried this:

Cumulative measure = CALCULATE(

    SUM(Table1[appointment_count]),

    FILTER(ALL(Table1),

    Table1[week_start_date]<=MAX(Table1[week_start_date])&&

    Table1[region]=SELECTEDVALUE(Table[region])

      ))

This gives the correct values when I filter by region, but now the default unfiltered value is wrong; it gives a very low figure that doesn’t relate to anything.

Thank you for any tips!

[RichardMo]

Great, thank you. That works when all my data is in the same table. I have another question - what if the filter was on a related region table?

[GuillaumePower]

Yes, you've right, I've to check that better.

[GuillaumePower]

It is not necessary to add  this filter "Table1[region]=SELECTEDVALUE(Table[region])" in the DAX command. You can use a slicer on the region which could select one or more region. You will get the right cumul.

[RichardMo]

When I had a slicer on region, or any other column, it didn't filter the cumulative chart. 

[GuillaumePower]

You should use this Dax Command whith AllExcept for the region :

Cumulative Mesure = calculate(sum(Feuil1[index]),filter(ALLEXCEPT(Feuil1,Feuil1[Region]),Feuil1[index]<=MAX(Feuil1[Index])))

Have you all your Data in the same Table ? That should run well like that.