cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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.

kikejnt_1-1623326875519.png

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
v-jingzhang
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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.

View solution in original post

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?

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.

061502.jpg

 

Regards,
Community Support Team _ Jing

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - July 22, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors