Hi Microsoft Forum Contributors and/or @tamerj1,
I'm trying to produce a measure called "Consecutive Month Under Budget" that gets its value from how many fiscal months in a row the month actual is below the month budget within a fiscal year (and then resets the calculation again for a new fiscal year).
Here's my sample data to help clarify my question:
Dimensions 1, 2, and Dim Date all have a 1 to many relationship with the fact table.
What's the best DAX to get this result? If it is easier, I can also merge the 1st and 2nd Dimension columns into the fact table so that the only 1 to many relationship the fact table has is just with the Dim Date.
| Value from 1st Dimension Column | Value from 2nd Dimension Column | Fiscal Year from DimDate | Fiscal Month from DimDate | Budget | Actual | Consecutive Month Under Budget |
| A | D | 2021 | 1 | 10 | 11 | 0 |
| A | D | 2021 | 2 | 10 | 10 | 0 |
| A | D | 2021 | 3 | 10 | 9 | 1 |
| A | D | 2021 | 4 | 10 | 9 | 2 |
| A | D | 2021 | 5 | 10 | 8 | 3 |
| A | D | 2021 | 6 | 10 | 15 | 0 |
| A | D | 2021 | 7 | 10 | 10 | 0 |
| A | D | 2021 | 8 | 10 | 12 | 0 |
| A | D | 2021 | 9 | 10 | 8 | 1 |
| A | D | 2021 | 10 | 10 | 7 | 2 |
| A | D | 2021 | 11 | 10 | 2 | 3 |
| A | D | 2021 | 12 | 10 | 8 | 4 |
| A | D | 2022 | 1 | 50 | 40 | 1 |
| A | D | 2022 | 2 | 50 | 30 | 2 |
| A | D | 2022 | 3 | 50 | 55 | 0 |
| A | D | 2022 | 4 | 50 | 60 | 0 |
| A | D | 2022 | 5 | 50 | 40 | 1 |
| A | D | 2022 | 6 | 50 | 25 | 2 |
| A | D | 2022 | 7 | 50 | 70 | 0 |
| A | D | 2022 | 8 | 50 | 40 | 1 |
| A | D | 2022 | 9 | 50 | 80 | 0 |
| A | D | 2022 | 10 | 50 | 90 | 0 |
| A | D | 2022 | 11 | 50 | 80 | 0 |
| A | D | 2022 | 12 | 50 | 40 | 1 |
| A | C | 2021 | 1 | 20 | 15 | 1 |
| A | C | 2021 | 2 | 20 | 15 | 2 |
| A | C | 2021 | 3 | 20 | 10 | 3 |
| A | C | 2022 | 1 | 10 | 5 | 1 |
| A | C | 2022 | 2 | 10 | 15 | 0 |
| A | C | 2022 | 3 | 10 | 10 | 0 |
| B | C | 2022 | 1 | 70 | 10 | 1 |
| B | C | 2022 | 2 | 70 | 80 | 0 |
| B | C | 2022 | 3 | 70 | 50 | 1 |
Thanks so much in advance.
