Use Variable to select Column

[KH_Mike]

Hi All,

Is it possible to use Variable to select sutiable column for calculation?

The scenarios is like below. I have two slice which contain [Fiscal Year] and [Year]. Right now my formula is like below

Last Year = 

Var LY = selectedvalue([Fiscal Year])-1

Return

Calculation(sum([Amount)],[Fiscal Year] = LY)

But this formula can only fulfill when user select [Fiscal Year]. I want to know if there is any formula can also work with [Year] by user selection. If user make the selection on [Fiscal Year], the the calculation will based on [Fiscal Year]. If user make the selection on [Year], the the calculation will based on [Year]. 

[amitchandak · Accepted solution]

@KH_Mike , You need to have two measures and switch them using a measure slicer using an independent table

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

Also, try to use Time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA