Calculate sales in previous month when working with financial calendar

[Anonymous]

Hi,

I have a financial calendar table that runs from October to September. 

When trying to calculate the previous month sales, I only get data for October, November and December. 

What would be the best DAX formula to create a previous month measure?

I'm currently using: 

Amount (Net) Last Month = calculate([Amount (Net)], PREVIOUSMONTH('DATE'[Date]))

Data should look like below:

Financial Month	Amount	Amount Previous Month
October	£3,594,007
November	£3,486,507	£3,594,007
December	£4,502,093	£3,486,507
January	£3,757,257	£4,502,093
February	£4,952,000	£3,757,257
March	£3,999,771	£4,952,000
April	£3,231,986	£3,999,771
May	£4,793,752	£3,231,986
June	£3,639,420	£4,793,752
July	£3,104,948	£3,639,420
August	£4,377,860	£3,104,948
September	£3,680,744	£4,377,860

But comes out as:

Financial Month	Amount	Amount Previous Month
October	£3,594,007	£3,680,744
November	£3,486,507	£3,594,007
December	£4,502,093	£3,486,507
January	£3,757,257
February	£4,952,000
March	£3,999,771
April	£3,231,986
May	£4,793,752
June	£3,639,420
July	£3,104,948
August	£4,377,860
September	£3,680,744

[Anonymous]

One more thing... You should always indicate which year you're talking about. January does not by itself tell you what year you're in. Please make sure you always know the year and Power BI does as well. This might be the source of your problems...

Best

Darek

[Anonymous]

If you have a custom calendar, and it looks like you do, then you CANNOT use time-intel functions. Easy as that. You have to create your own logic. There are some time-intel functions that DO work with a financial calendar and they take an additional argument that tells them when a financial year ends (like, say, in June).

If you go to the documentation of PREVIOUSMONTH, you'll read:

This function returns all dates from the previous month, using the first date in the column used as input. For example, if the first date in the dates argument refers to June 10, 2009, this function returns all dates for the month of May, 2009.

So, as you see, it only works with the standard calendar, not a financial one. To do what you need, you probably hav to visit www.sqlbi.com and find an article(s) on how to work with custom calendars.

Best

Darek