cancel
Showing results for
Did you mean:
Highlighted
CharlvdM 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: 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

## 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)```

3 REPLIES 3
jared_farber Member

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

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

## 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)```

Announcements #### 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?

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

We are super excited for our update this month, as we are releasing two of our top community requests! #### Power Platform Online Conference 