cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CharlvdM Frequent Visitor
Frequent Visitor

Calculate downtime for latest week vs target

Hi

 

I am working with downtime data for a manufacturing plant. I would like to create a KPI visual that indicates whether the duration of downtime is less or more than the target for the past week (sum of minutes of all machines). I have managed to create the following visual:

KPI.PNG

 

The indicator of this visual is a measure that I created:

Downtime This Week = 
CALCULATE(SUM('All Events'[Duration]), 'All Events'[Weeknum]=29)

 

At the end of each week, I will be updating the data to include the past week's data. I would like the KPI visual to automatically update for the latest week (i.e. from week 29 to week 30). I have tried to do it this way,

Downtime This Week = 
CALCULATE(SUM('All Events'[Duration]), 
'All events'[Weeknum]= CALCULATE(MAX('All Events'[Weeknum]),ALL('All Events'[Weeknum])))

but I receive an error message saying "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

How can I filter my Calculate expression for the latest week?

1 ACCEPTED SOLUTION

Accepted Solutions
CharlvdM Frequent Visitor
Frequent Visitor

Re: Calculate downtime for latest week vs target

Thank you very much for the response @mussaenda.

 

Your solution works if the maximum week number corresponds to the current date, which it doens't in my situation. I managed to solve the problem by creating a new column in my table with the following expression:

ScenarioID = 
IF([Weeknum]=MAX([Weeknum]),1,IF([Weeknum]=MAX([Weeknum])-1,2,3))

ScenarioID will contain 1 if it corresponds with the maximum week number (29 in this example), 2 for the second largest (to compare this week against last week) and 3 for all others. The downtime for this week can then be calculated with the following expression:

Downtime This Week = 
CALCULATE(SUM('All Events'[Duration]), 'All Events'[ScenarioID]=1)

 

 

View solution in original post

3 REPLIES 3

Re: Calculate downtime for latest week vs target

Hello-

 

   Instead of filtering it for week 29, create a seperate measure when you choose the Max(week).  It will auto increase to the maximum week.  Then place that measure in your current measure.

 

Jared

mussaenda New Contributor
New Contributor

Re: Calculate downtime for latest week vs target

Downtime This Week = 
CALCULATE(SUM('All Events'[Duration]), 
Filter(All events,
'All events'[Weeknum]= CALCULATE(MAX('All Events'[Weeknum]),ALL('All Events'[Weeknum]))))

this is causing the error. You need to add a Filter function

 

and for your second question, you want to see the value for latest week, right? try this

Downtime This Week = 
CALCULATE(
     SUM('All Events'[Duration]), 
          Filter(All events,
              'All events'[Weeknum]= weeknum(today(), 2)
          )
)
CharlvdM Frequent Visitor
Frequent Visitor

Re: Calculate downtime for latest week vs target

Thank you very much for the response @mussaenda.

 

Your solution works if the maximum week number corresponds to the current date, which it doens't in my situation. I managed to solve the problem by creating a new column in my table with the following expression:

ScenarioID = 
IF([Weeknum]=MAX([Weeknum]),1,IF([Weeknum]=MAX([Weeknum])-1,2,3))

ScenarioID will contain 1 if it corresponds with the maximum week number (29 in this example), 2 for the second largest (to compare this week against last week) and 3 for all others. The downtime for this week can then be calculated with the following expression:

Downtime This Week = 
CALCULATE(SUM('All Events'[Duration]), 'All Events'[ScenarioID]=1)

 

 

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors