Forum Discussion

rocky84's avatar
rocky84
Regular Visitor
3 years ago

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...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
    rocky84
    Regular Visitor
    3 years ago

    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!