CUMULATIVE MEASURE OVER TIME FOR EACH YEAR

[Syndicate_Admin]

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!

[v-jingzhang · Accepted solution]

Hi @Syndicate_Admin 

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.

[Syndicate_Admin]

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?

[v-jingzhang]

Hi @Syndicate_Admin 

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