Greg_Deckler
8 years agoCommunity Champion
Periodic Revenue (Reverse YTD)
Imagine you have a table that only includes YTD Revenue such as this:
RevenueYTD
Year Month Revenue YTD
| 2017 | 11 | $30 |
| 2017 | 12 | $50 |
| 2018 | 1 | $40 |
| 2018 | 2 | $60 |
| 2018 | 3 | $110 |
You wish to reverse engineer the montly revenue figures. You can use a measure such as this:
Periodic Revenue =
VAR MyMonth = MAX(RevenueYTD[Month])
VAR MyYear = MAX(RevenueYTD[Year])
RETURN
SUM(RevenueYTD[Revenue YTD])
- CALCULATE(
SUM(RevenueYTD[Revenue YTD])
,FILTER(
ALL(RevenueYTD)
,RevenueYTD[Month]=MyMonth-1 && RevenueYTD[Year]=MyYear
)
)
eyJrIjoiNzEzZDZlOWQtOWQ1Zi00MTlkLTk0ODgtNzgxN2RlMDkwY2QwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
2 Replies
- AnonymousNot applicable
- AnonymousNot applicableThank you can this be done also in power query?