Need help with Current Month and Prior Month

[rocky84]

Hello,

Hoping some Power BI experts can help me with the following task, which is crucial for Financial analysis.

I have a set of financial information which I update monthly with a new batch of numbers.

I already have a date calendar query build in, but what I would like is a new column which will show "Current Month", "Prior Month" or "Other".

Current month should be the most recent but 1 month (i.e. if we are in February 2023, "Current Month" should be "January 2023").

Likewise, "Prior Month" should be "December 2022)

Am sure this has been asked before but I can't seem to apply any solutions to my challenge.

thanks in advance! 😀

[amitchandak]

@rocky84 , if you need based on today, assume no filter

use

This Month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

Last Month Today =
var _min = eomonth(today(),-3)+1
var _max = eomonth(today(),-2)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

or

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),,DATESMTD(dateadd('Date'[Date],-1,MONTH))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[rocky84]

Thanks for the guidance (and apologies for my delayed response)

I think this would work for sure, but it doesn't look like it considers the year at all? Only the month?

So ideally it should say "ok today is 28 Feb 2023", therefore the current, most recent closed reporting month is "jan 2023". Also, the prior month, would be december 2022, so the formula should realise that this is 2022, not 2023. Does that makes sense?

thanks again for your valued insight!