cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gauß452 Frequent Visitor
Frequent Visitor

Value on last day of fiscal month

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Floriankx Established Member
Established Member

Re: Value on last day of fiscal month

Hello,

 

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

 

FiscalMonthDateFiscalMonthNumb
Mai04.05.20185
Mai02.06.20185
.........

 

So you could 

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

and

 

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

View solution in original post

1 REPLY 1
Floriankx Established Member
Established Member

Re: Value on last day of fiscal month

Hello,

 

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

 

FiscalMonthDateFiscalMonthNumb
Mai04.05.20185
Mai02.06.20185
.........

 

So you could 

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

and

 

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,705)