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 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! ðŸ˜€

Need Help

2 Replies

  • 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!