Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EricPierre
Frequent Visitor

Context to get Year to Date count

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?

 

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Incident ID and Date info.Incident ID and Date info.Date Dimension TableDate Dimension TableSlicers for Year and Month. Filter (Top2) on Incident TypeSlicers 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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.