How to get the last Period Month from selected value in drop down list of Current Month

[giofranc]

I have a request, my table data set have columns "FiscalMonth" 1,2,...12...

FiscalYear: 15,16,17...20

Amount = Totals (Already Calculated from dB)

So I want to display the current period amount and last period Amount

I was able to accomplish only the current Period using this formula

CurrentPeriod = CALCULATE(SUM(GLTotals[Amount]),FILTER(GLTotals,GLTotals[FiscalPeriod]=SELECTEDVALUE(GLTotals[FiscalPeriod])))

I need some help on how to retrieve LastPeriod on a matrix.

This is the table dataset. 

E.G If I selected 12 in the drop down, the LastPeriod has to be calculation of FiscalMonth11

LastPeriod ==?

Thank you for your help

[amitchandak · Accepted solution]

A calculation like this can work. but better you have a date calendar table and then do date calc on calendar table.

last Period =
var _period1= maxx(GLTotals,SELECTEDVALUE(GLTotals[FiscalPeriod]))
var _year1= maxx(GLTotals,SELECTEDVALUE(GLTotals[FiscalYear]))
var _period = if(_period1=12, 1, _period1-1)
var _year = if(_period1=12, _year-1, _year)
 

CALCULATE(SUM(GLTotals[Amount]),FILTER(all(GLTotals),GLTotals[FiscalPeriod]=_period1 && GLTotals[FiscalYear] =_year ))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Greg_Deckler]

You would use the time intelligence functions or see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...