cancel
Showing results for 
Search instead for 
Did you mean: 

Re: Periodic Revenue (Reverse YTD)

Frequent Visitor
630 Views
Super User
Super User

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Corrado84 Frequent Visitor
Frequent Visitor

Re: Periodic Revenue (Reverse YTD)

Hi Greg,

 

Very  nice!! Smiley Very 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

Highlighted
cafeaulait Frequent Visitor
Frequent Visitor

Re: Periodic Revenue (Reverse YTD)

Thank you can this be done also in power query?