How to calculate multiple rows for a condition

[sam.mckay]

I understand this can be confusing and quite a lot of people get caught on this.

It’s all about dealing with multiple dates in your fact table, like you are here with the workflow process.

The key here is a two-step process. First, you need to get the data model correct then, use the right DAX measure. It’s not too difficult once you know the technique here.

The technique is detailed here. It’s not the exact scenario you have but the technique of dealing with multiple dates is exactly the same.

Sales and Orders in Progress

The pattern is called ‘Events In Progress’, which is essentially what you are looking for here.

You must use the inactive relationships like the below.

You may have multiple of these if you have lots of dates.

Then you need to use this pattern of formula to get all the calculations you require

Again you might have to have many of similar formulas that look like this as it looks like you have lot’s of different calculations across many different dates.

See how you go with these techniques.

Here’s some other questions that are similar to this one that I’ve answered previously.

Employee Headcount

Sure happy to look into this but need to know more. There’s a lot at play here so need to understand the data within the tables, how you setup your data model including the relationships you have and also what is the current context of the calculation. Unfortunately with DAX it’s never a simple as just reviewing the formula by itself, there always more to it. If you want to add a demo model here with the scenario then that will speed up assistance. Chrs Just as a side note on this as well a…

Staff Population

Have you had a chance to work through this workshop here as this covers many of these calculations for HR data. As with anything here the data matters a lot. So with your data you have to manage multiple dates…very doable. This is where the ‘events is progress’ pattern comes into play. See below for the exact technique to use from a modelling and DAX formula perspective. This is a different scenario exampled above but the technique is exactly the same. I’ve worked on something briefly h…

Show events based on opening date as well as closing date

This is the exact technique you want to review with this. See below - It’s called the Events In Progress technique Follow the technique here exactly and you’ll see how you can calculated both of these. The key is to understand how the USERELATIONSHIP function works. This is a key function that has some very useful usages. So a great one to review in-depth.