Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
03-09-2018 07:01 AM - last edited 06-27-2018 13:32 PM
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