Context to get Year to Date count

[EricPierre]

I'm building a report to provide the summary of the month in counts of various items. I have 2 slicers to select Year and Month, as well as an visualization filter on Incident Type to display only the top 5 counts.

Now, I'm trying to calculate the YTD Count. I figured I needed to do a count, where I remove the context of the Month slicer and the Incident Type filter.  I came up with the following:

Incident YTD Count = CALCULATE(
                                          DISTINCTCOUNT('Incident Information'[Incident Number]),
                                          all('Incident Information'[Incident Type]),
                                          ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]))

This properly calculates the total for the year (it correctly removed the month slicer and the top 5 filters). Now, I needed to add a filter to only take the months before the selected one. So I added the following:

Incident YTD Count = CALCULATE(
                                          DISTINCTCOUNT('Incident Information'[Incident Number]),
                                          all('Incident Information'[Incident Type]),
                                          ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]),

                                          FILTER('Incident Start Date','Incident Start Date'[Month Number]<=[Selected Month]))

However, this just gives me the total of the selected month. It is as if the Filter still have the context for the month slicer.

What am I missing?

[v-qiuyu-msft]

Hi @EricPierre,

Please share some sample data and clarify desired results. It would be better if you can share sample pbix file. 

Best Regards,
Qiuyun Yu 

[EricPierre]

Incident ID and Date info.Date Dimension TableSlicers for Year and Month. Filter (Top2) on Incident Type

Hello Qiuyun Yu,

Currently, in the table, I have Total of incident in selected month and % per type. These work perfectly.

Now, I would like to add a column showing the YTD count per incident. As mentioned in my original post, I am able to remove the filter by incident type (Top 2) and keep the filter for Year. However, when I try to add a filter for Date smaller than selected, it appears the month selection set in the slicer still applies.

In numbers, for February 2016, I have 237 incidents. (1 doesn't show up as it is not in the top 2, % indicates that by not adding to 100%).

The following gives me 237 (as if last line has a context of only the data found using the slicer):

Incident YTD Count = CALCULATE(
   DISTINCTCOUNT('Incident Measures'[Incident Number]),
   ALL('Incident Information'[Incident Type]),
   ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]),
   FILTER('Incident Start Date','Incident Start Date'[Date]<=[Selected Month]))

thanks,

EricPierre

[EricPierre]

Furthermore, the following gives me the total for the full year (as I would have expected), which is 3386:

Incident Year Count = CALCULATE(
   DISTINCTCOUNT('Incident Measures'[Incident Number]),
   ALL('Incident Information'[Incident Type]),
   ALLEXCEPT('Incident Start Date','Incident Start Date'[Year]))

My idea was that if I add a filter stating that I wanted only the months before the selected month, that would do the trick. But when I add a filter for month, then the slicer selection seems to apply, and I only get the selected month total.