Trying to create a rolling 10 day margin measure that will return the average margin percentage for the previous 10 days. I was using the DAX below but does not exclude accounts that did not have any activity in the last 10 days. It returns the last 'Margin %' date even if it was outside of 10 days. Sample data sheet below "Business Inc" should not return a value. I think I should not use LASTDATE but I'm not sure how to make this work with the TODAY() function.
10 Day Rolling Avg =
CALCULATE (
[Margin %],
DATESBETWEEN (
'Merge1'[Date],
DATEADD ( LASTDATE ( 'Merge1'[Date] ), -10, DAY ),
LASTDATE ( 'Merge1'[Date] )
)
)
Margin % is a measure DAX below.
Margin% =
DIVIDE(
SUM('Data'[Profit]),
SUM('Data'[Revenue])
)
10 Day Rolling Avg =
CALCULATE (
[Margin %],
DATESBETWEEN (
'Merge1'[Date],
DATEADD ( LASTDATE ( 'Merge1'[Date] ), -10, DAY ),
LASTDATE ( 'Merge1'[Date] )
)
)
Margin % is a measure DAX below.
Margin% =
DIVIDE(
SUM('Data'[Profit]),
SUM('Data'[Revenue])
)
| Account | Cost | Revenue | Profit | Date |
| Business Inc | 100 | 150 | 50 | 10/1/2023 |
| Corporation | 150 | 200 | 50 | 10/1/2023 |
| The Firm | 100 | 150 | 50 | 10/1/2023 |
| Business Inc | 200 | 300 | 100 | 10/2/2023 |
| Corporation | 50 | 200 | 150 | 10/2/2023 |
| The Firm | 50 | 150 | 100 | 10/2/2023 |
| Corporation | 75 | 50 | -25 | 10/3/2023 |
| The Firm | 75 | 75 | 0 | 10/3/2023 |
| Corporation | 100 | 50 | -50 | 10/6/2023 |
| The Firm | 100 | 50 | -50 | 10/6/2023 |
| Corporation | 300 | 150 | 150 | 10/12/2023 |
| The Firm | 300 | 100 | 200 | 10/12/2023 |
| Corporation | 300 | 150 | 150 | 10/13/2023 |
| The Firm | 300 | 100 | 200 | 10/13/2023 |
