cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Need help with Current Month and Prior Month

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.

2 REPLIES 2
Super User

@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

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

Regular Visitor

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors