Forum Discussion

cheid's avatar
cheid
Frequent Visitor
10 months ago
Solved

Prior Month/Year Measures Not Working

I have a card that I am inputting current month/year data and then doing a comparison against the same time period from the previous month/year.  The problem I am having is that the previous month/year calculation is giving me a total for the entire previous month and not the month to date total I was expecting.  All the previous month totals below are for the month and not through the 22nd of the previous month.  How do I get these measures to pull the previous month total correctly? Thanks.

Month To Date

Total Hours - MTD =
CALCULATE(
    [Total Hours],
    DATESMTD('Lookup Calendar'[Date])
)
 
Prior Month To Date
Total Hours - PMTD =
CALCULATE(
    [Total Hours - MTD],
    DATEADD('Lookup Calendar'[Date],-1,MONTH)
)
Need Help
  • v-hjannapu's avatar
    v-hjannapu
    10 months ago

    Hi cheid,

    Glad to know you found a logic that seems to be working. If the issue is resolved, that’s great. If you still face any problems, please feel free to reach out to us again  we will be happy to help.

    Regards,
    Community Support Team.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    10 months ago

    cheid , Try like 

     

    Month To Date

    Total Hours - MTD =
    CALCULATE(
        [Total Hours],
        DATESMTD(dateadd('Lookup Calendar'[Date], -1, Month)
    )
    • cheid's avatar
      cheid
      Frequent Visitor
      10 months ago

      Thanks for the reply.  I tried that as well and it gives me the total for the entire previoius month.

      • amitchandak's avatar
        amitchandak
        Super User
        10 months ago

        cheid , Reason for that
        Complete the month that is selected on the Slicer. Or the Month is there in the visual row


        LMTD QTY forced=
        var _max = today()
        return
        if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,month) )), blank())


        We can force it

        LMTD QTY forced=
        var _max = date(year(today()),month(today())-1,day(today()))
        return
        if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
        //OR
        //CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
        //TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


        or

        LMTD QTY forced=
        Var _Maxdate = MAX('Date Table'[Date])
        var _max = date(year(_Maxdate),month(_Maxdate)-1,day(_Maxdate))
        return
        if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
        //OR
        //CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
        //TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

         






  • Poojara_D12's avatar
    Poojara_D12
    Super User
    10 months ago

    Hi cheid 

    The issue you’re encountering is common when comparing month-to-date (MTD) values with a prior month’s MTD value in Power BI. The reason your “Prior Month To Date” measure is returning totals for the entire previous month rather than up to the same day of the prior month (for example, through the 22nd) is because the DATEADD function shifts the entire date context by one month, but it doesn’t dynamically align to the current month’s date range. To fix this, you need to explicitly limit the calculation for the prior month to the same number of days as the current MTD period. One effective way is to capture the last date of the current MTD period and then filter the previous month’s dates up to that same day number. You can modify your measure like this:

    Total Hours - PMTD =
VAR CurrentMTDLastDate = MAX('Lookup Calendar'[Date])
VAR PriorMonthSameDate =
    EOMONTH(CurrentMTDLastDate, -1) + DAY(CurrentMTDLastDate)
RETURN
CALCULATE(
    [Total Hours],
    DATESBETWEEN(
        'Lookup Calendar'[Date],
        STARTOFMONTH(EOMONTH(CurrentMTDLastDate, -1)),
        PriorMonthSameDate
    )
)

    This approach dynamically identifies the same cutoff day in the previous month and restricts the prior month’s total to that period only. As a result, if today is the 22nd of the current month, the “Prior Month To Date” measure will correctly calculate values through the 22nd of the previous month, ensuring an accurate month-to-date comparison between the two periods.