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/ye...
Need Help
  • Anonymous's avatar
    Anonymous
    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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)

     






    • cheid's avatar
      cheid
      Frequent Visitor
      10 months ago

      Thanks again for the reply. I failed to mention that there is no slicer that impacts the card I am creating.  I did come up with new logic that does seem to be working.  

      Total Hours - PMTD =
      TOTALMTD([Total Hours],
      DATEADD(
          FILTER(
              DATESMTD('Lookup Calendar'[Date]),
              'Lookup Calendar'[Date]<TODAY()
          ),
          -1,
          MONTH
      )
      )
      • Anonymous's avatar
        Anonymous
        Not applicable
        10 months ago

        Hi cheid

        Good to know you found the solution. Yes, the formula you used is the right way when you want to show only the month-to-date value for the previous month. It helps to match the same date range instead of taking the full month total.

        Thanks for sharing your working logic, it will help others too.


        Regards,
        Community Support Team.