Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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
            )
      )

 

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    Thank you can this be done also in power query?