Total count of incidents from last 5 years.

[Anonymous]

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?

[amitchandak]

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

[Anonymous]

Hi @amitchandak Thanks for the reply.

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

https://github.com/Adityadambal/PowerBI.git

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

[v-cgao-msft]

Hi @Anonymous ,

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

[Anonymous]

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

https://github.com/Adityadambal/PowerBI.git