cancel
Showing results for
Did you mean:

## CUMULATIVE MEASURE OVER TIME FOR EACH YEAR

Good community!

I've created a basic .pbix bringing me only the table and the three measures it takes to deal with the problem I have. I can't share the rest.

I have two measures: TRIR_ and LTIF_ which are a calculation of the people who have had an accident at a point in time versus the hours they have worked. I painted a table with these two measures and the hours worked based on the sort date (concatenation of the year and month of the actual date that is in the table).

What I want is to have a measure that accumulates the TRIR_ and the LTIF_ during the year and that at the end of the year in the month of December gives me a value; and start counting from 0 to accumulate when it passes to the next year. For example, at the height of where I put the red circle should put the TRIR_ and LTIF_ accumulated from January 2018 to December 2018; and where I put the blue circle should start to accumulate the year 2019 from 0, regardless of what happened in 2018.

Sorry if I don't explain myself.

Then when I have this calculation, I want to make a graph of grouped columns and lines, where in the columns put the "Work Hours" and in the lines the ACCUMULATED that we have calculated for LTIF_ and TRIR_.

attached .pbix.

https://1drv.ms/u/s!AggHtWEAB2tKgV4zqe_zwSzFzEPz?e=nA8YI8

Thank you!

1 ACCEPTED SOLUTION
Community Support

You could add a Year column into the table, then create a measure like below. I take [TRIR_] for example. You could replace it with [LTIF_].

``````Cumulative TRIR_ =
VAR __yearmonth = SELECTEDVALUE('Máster Dónde y Qué'[Fecha ordenación])
VAR __year = SELECTEDVALUE('Máster Dónde y Qué'[Year])
RETURN
CALCULATE([TRIR_],ALL('Máster Dónde y Qué'),'Máster Dónde y Qué'[Year]=__year,'Máster Dónde y Qué'[Fecha ordenación]<=__yearmonth)+0``````

For the graph, you could use a Line and Clustered column chart.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

3 REPLIES 3
Community Support

You could add a Year column into the table, then create a measure like below. I take [TRIR_] for example. You could replace it with [LTIF_].

``````Cumulative TRIR_ =
VAR __yearmonth = SELECTEDVALUE('Máster Dónde y Qué'[Fecha ordenación])
VAR __year = SELECTEDVALUE('Máster Dónde y Qué'[Year])
RETURN
CALCULATE([TRIR_],ALL('Máster Dónde y Qué'),'Máster Dónde y Qué'[Year]=__year,'Máster Dónde y Qué'[Fecha ordenación]<=__yearmonth)+0``````

For the graph, you could use a Line and Clustered column chart.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

The solution is very good and serves me well for the graph.

But I have tried to take advantage of the measure to paint the calculation on a card and have it tell me the accumulated LTIF in 2021 and the accumulated TRIR in 2021 and it gives me error.

I think I'm not doing something right. I understand that the "ordination date" should not be taken into account there.

What would both calculations look like for a card and for it to be ACCUMULATED in 2021?

Community Support

You could use MAX to replace SELECTEDVALUE in the measure like below. This measure will work in the table visual. When in a card visual, it will calculate for 2021 as 2021 is the latest year value in your table.

``````Cumulative TRIR_ 2 =
VAR __year = MAX('Máster Dónde y Qué'[Year])
VAR __yearmonth = MAX('Máster Dónde y Qué'[Fecha ordenación])
RETURN
CALCULATE([TRIR_],ALL('Máster Dónde y Qué'),'Máster Dónde y Qué'[Year]=__year,'Máster Dónde y Qué'[Fecha ordenación]<=__yearmonth)+0``````

But for your current sample data, both of them will return 0 because TRIR and LTIF columns have blank values for all rows in 2021 while your original [TRIR_] and [LTIF_] measures filter out blank values.

Regards,
Community Support Team _ Jing

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.