Cumulative count which ignores Date slicer but honors rest

[rajanikanthr]

I have data similar to this and currently have a measure which calculates distinct row count for each feature and until that date.

CumulativeRegistrations = CALCULATE(DISTINCTCOUNT(Previews[SubscriptionId]), FILTER (ALLEXCEPT(Previews, Previews[Feature]), Previews[Date] <= MAX(Previews[Date])))

I have an area chart with axis is Date , legend is feature and Values is the measure. and it's working perfectly.

But I have now added slicers for Date, customer and feature but the chart needs to honor these except Date in calculation.

Say I have selected Customer C1 and Feature F1 and Date slicer to start from 11/2. The value I want is 2 instead of 1, because there are 2 records in the table with C1 and F1 combination, as I changed formula to use ALLSELECTED which is skipping filtered out data.

How can I change formula to use filters for Feature and Customer but not Date. I still want the date slicer to be there so that chart shows the selected date range only.

[Ashish_Mathur · Accepted solution]

Hi,

1. Create a Calendar Table
2. Create a relatioship from the Date column in your base Data Table to the Date column in the Calendar Table
3. In your visual, drag the Date column from the calendar table in your Filter/slicer.  Select 11/2/2017 in the slicer
4. Write this measure

=CALCULATE(COUNTROWS(Data),DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),MAX(Calendar[Date])))

Hope this helps.

[rajanikanthr]

it's working fine with Calendar table, but the date slicer shows the maximum date from Calendar.. is there any way I can restrict to Max date from the Data table so that it's easy for all kind of users in terms of scrolling the slicer .

[Ashish_Mathur]

Hi,

The Calendar table should be created using the New Table feature of PowerBI desktop (under modelling).  Write this formula there

=CALENDAR(MIN(Previews[Date]),MAX(Previews[Date]))

[rajanikanthr]

perfect... worked fine..

[Ashish_Mathur]

Thank you.

[MattAllington]

I suggest you creat a calendar table and use the date column in that table instead of the previews table. That way when you can just remove the filter from the calendar table, and nothing else simple using ALL.