Value on last day of fiscal month

[Gauß452]

Hey guys, 

i have a question about Time-Intelligence functions.

I created a 544 fiscal calenar in powerQuery and now I am looking for a way to compare values on the last day of each month. 

My fiscal calendar starts on sunday --> May is 06.05.2018 - 02.06.2018

Now I want to compare the values of 02.06.2018 and 05.04.2018.

Does anybody know, how to build that formula?

Thank you

Gauß452

[Floriankx · Accepted solution]

Hello,

Do you have your Fiscal Month as column? I recommend to add FiscalMonthNumber as basis to calculate:

FiscalMonth	Date	FiscalMonthNumb
Mai	04.05.2018	5
Mai	02.06.2018	5
...	...	...

So you could 

LastDay:=CALCULATE([Measure];LASTDATE(FactTable[Date])) 

and

LastDayPM:=VAR PrevMonth=MAX(FactTable[FiscalMonthNumber])-1
RETURN
CALCULATE([LastDay];FactTable[FiscalMonthNumber]=PrevMonth)