Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Total count of incidents from last 5 years.

I am looking to get the total count of incidents for the past 5 years for 2023, 2022, 2021,.....

I used the measure below to calculate the count total.

Dept L5 = CALCULATE(CALCULATE( COUNT(Incidents[IncidentRefNo]),dates[date] >= TODAY() - 1825),FILTER('Incidents',Incidents[Merged] = "Department"))

However, when I bring my date[year] into it, it just splits the total into those years.



How can i change the above measure to give me count of incidents when mapped against years?

Super User
Super User

@adambal , what is actual Vs expected result ?


Try like

Dept L5 = CALCULATE(CALCULATE( COUNT(Incidents[IncidentRefNo]),filter(dates dates[date] >= TODAY() - 1825)),FILTER('Incidents',Incidents[Merged] = "Department"))



If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Hi @amitchandak Thanks for the reply.


I have uploaded the file (QnC.pbix) here.

This is how it looks. I want 2023 to have 219 for Alkathene L5, and 2022 should have all values from 2017 to 2022. and so on.. 



Hi @adambal ,


I have rewritten one of the measures, please check if it is the expected output for you.



Alkathene L5(2) = 
VAR _max_date =
    MAX ( '1CALENDAR'[Date] )
VAR _result =
    SUMX (
        SUMMARIZE (
                CALCULATE (
                    COUNT ( QNCIncidents[IncidentRefNo] ),
                    FILTER ( dates, dates[Date] >= TODAY () - 1825 ),
                    FILTER ( 'QNCIncidents', QNCIncidents[Merged] = "Alkathene" ),
                    '1CALENDAR'[Date] <= _max_date





Best Regards,

Community Support Team


If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

@v-cgao-msft , Thanks for the response.


I had an additional request on calculating Normalisation % if you an provide me with some help.


I have a Normalisation equation like, Norm Alkathene = [LPI Alkathene]*100/[Alkathene L5]


I have also attached the new "QnC Updated.pbix" file which contains this table for all materials.


The trouble I am having with my calculation measure is, 


1. When I apply date filter, the values that are coming are different. 

2. I want the last 5 years count to stay constant for the year, i.e., it should be 219 even when I drill down to 2023 Quarters for Norm calculations.

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors