topic Re: Periodic Revenue (Reverse YTD) in Quick Measures Gallery

Periodic Revenue (Reverse YTD)

Greg_Deckler — Wed, 27 Jun 2018 20:32:52 GMT

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

Re: Periodic Revenue (Reverse YTD)

Anonymous — Tue, 17 Jul 2018 14:31:23 GMT

Hi Greg,

Very nice!! :smileyvery-happy:

I have the question: if I want know the Total (sum) of Periodic Revenues?

If I enable display of the total in PowerBI, these are the results.

Total Revenues YTD = $290

Total Periodic Revenus = $290 (it' wrong)

I ask you for help.

Thank you

Re: Periodic Revenue (Reverse YTD)

Anonymous — Wed, 12 Dec 2018 22:03:30 GMT

Thank you can this be done also in power query?