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.
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?
@adambal , what is actual Vs expected result ?
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.
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 ( 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
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.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.