Calculate revenue and same period last year for any selected year

[Gareth_Hunt]

Hi Guys appreciate this may not have been the best way to setup the data initially but in have invoice data spanning several years. I have a DCalander table an linked the 2.

From then I created hard coded measures to calculate the revenue for the this current year as 

CY-Revenue = CALCULATE(sum('InvoiceData'[Revenue])

, FILTER(DCalandar,AND(

DCalandar[Date]>=Date(Year(TODAY()),01,01),

DCalandar[Date]<=DATE(YEAR(TODAY()),12,31))))

and the Previous year as 

PY-Revenue = CALCULATE(sum('InvoiceData'[Revenue])

, FILTER(DCalandar,AND(

DCalandar[Date]>=Date(Year(TODAY()-1),01,01),

DCalandar[Date]<=Date(Year(TODAY()-1),12,31))))

However I have been now asked to increase the functionality such that for any selected year selected in filter(DCalander) the revenue data would show the selected year (as current year) and prior year values acordingly. 

Appreicaiate any advice on how to redefine my revenue measures to make this possible.

With best regards

Gareth

[V-pazhen-msft]

@Gareth_Hunt 

I guess you need add All(Table) to the filter for previousyear measure.

SumRevenue = SUM(InvoiceData[Revenue])

PreviousYrSumRevenue = CALCULATE([SumRevenue], PREVIOUSYEAR(DCalendar[Date]),All(DCalendar))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

If the InvoiceData is related to the date dimension correctly, this looks like 2 measures are required

SumRevenue = SUM(InvoiceData[Revenue])

PreviousYrSumRevenue = CALCULATE([SumRevenue], PREVIOUSYEAR(DCalendar[Date])

[Gareth_Hunt]

Hi thank you for your feedback, the issue i have is that the total revenue sums all revenues for both years

should I then calculate the year selected as being this year = SUM revenue-LYrevenue?

[V-pazhen-msft · Accepted solution]

@Gareth_Hunt 

With the above formulas, once you selected the Year in the slicer, the measures should only sum the selected year revenue for the first measure, and sum the previous year revenue of the second measure. Additional calculation is not required.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.