cancel
Showing results for
Did you mean:
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?

4 REPLIES 4
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! !!
Helper III

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..

Community Support

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 (
ALLSELECTED('1CALENDAR'),
'1CALENDAR'[Date].[Year],
'1CALENDAR'[Date].[Quarter],
"L5",
CALCULATE (
COUNT ( QNCIncidents[IncidentRefNo] ),
FILTER ( dates, dates[Date] >= TODAY () - 1825 ),
FILTER ( 'QNCIncidents', QNCIncidents[Merged] = "Alkathene" ),
'1CALENDAR'[Date] <= _max_date
)
),
[L5]
)
RETURN
_result``````

Result.

Best Regards,
Gao

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

Helper III

@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.