Forum Discussion

cheid's avatar
cheid
Frequent Visitor
7 months ago
Solved

Previous Month's MTD calculation

I have a dashboard where I have calculated the current MTD order count. I would like to create a measure that calculates the previous months MTD throught the same time period of the current month.  I have tried multiple different measures, but I always get the full month total.  Below are to measures I used, but neither worked.   How do I only get the previous month's MTD order count through the current day of the current month?  Your help is greatly appreciated.

 

Last MTD Sales = CALCULATE(
TOTALMTD(DISTINCTCOUNT('Repair Orders'[Order Number]),'LookUp - Calendar'[Date]),
DATEADD('LookUp - Calendar'[Date],-1,MONTH)
)



Last MTD Sales = 
CALCULATE(
    DISTINCTCOUNT('Repair Orders'[Order Number]),
    DATESMTD(DATEADD('LookUp - Calendar'[Date], -1, MONTH))
)

 

Need Help
  • samratpbi's avatar
    samratpbi
    7 months ago

    Hi cheid , first of all, I have created a table with some sample data. Then created below measure for MTD check:

    Total Value MTD =
    TOTALMTD(
        [Total Value],
        Date_Line[Date]
    )
     
    Then created below measure for Previous Month to Date:

    Total Value PMTD =
    VAR _maxDate = MAX(Date_Line[Date])
    VAR _prevMStart = EOMONTH(_maxDate, -2) + 1
    VAR _prevFMEnd = EOMONTH(_maxDate, -1)
    VAR _PrevMEnd =
    DATE(
        YEAR(_prevMStart),
        MONTH(_prevMStart),
        MIN(DAY(_maxDate), DAY(_prevFMEnd))
    )
    RETURN
    CALCULATE(
        [Total Value],
        DATESBETWEEN(Date_Line[Date],_prevMStart,_PrevMEnd)
    )
     
    This measure with return PMTD based on latest month's max date.
    Ideally you should use a date table with continuos dates and defined as Date table. However above measure should be working fine.
    Below is the screenshot:
     
    Hope this helps to resolve your problem.
    If it does, then please mark it as solution.
     
    Thanks - Samrat

5 Replies

  • samratpbi's avatar
    samratpbi
    Icon for Super User rankSuper User
    7 months ago

    Hi cheid , first of all, I have created a table with some sample data. Then created below measure for MTD check:

    Total Value MTD =
    TOTALMTD(
        [Total Value],
        Date_Line[Date]
    )
     
    Then created below measure for Previous Month to Date:

    Total Value PMTD =
    VAR _maxDate = MAX(Date_Line[Date])
    VAR _prevMStart = EOMONTH(_maxDate, -2) + 1
    VAR _prevFMEnd = EOMONTH(_maxDate, -1)
    VAR _PrevMEnd =
    DATE(
        YEAR(_prevMStart),
        MONTH(_prevMStart),
        MIN(DAY(_maxDate), DAY(_prevFMEnd))
    )
    RETURN
    CALCULATE(
        [Total Value],
        DATESBETWEEN(Date_Line[Date],_prevMStart,_PrevMEnd)
    )
     
    This measure with return PMTD based on latest month's max date.
    Ideally you should use a date table with continuos dates and defined as Date table. However above measure should be working fine.
    Below is the screenshot:
     
    Hope this helps to resolve your problem.
    If it does, then please mark it as solution.
     
    Thanks - Samrat
    • cheid's avatar
      cheid
      Frequent Visitor
      7 months ago

      Thanks for your help.I will give it a try when I am back in the office tomorrow.  I do have a continuous date table which is why I find it odd that none of the measures I created worked to calculated prior month's MTD order count.   I will let you know if this works.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    7 months ago

    hi cheid ,

     

    You may also try like below, instead of time intelligence functions:

    Last MTD Sales =
    VAR _date=MAX('LookUp - Calendar'[Date])
    VAR _result =
    CALCULATE(
    DISTINCTCOUNT('Repair Orders'[Order Number]),
    FILTER(
    ALL('LookUp - Calendar'[Date]),

    EOMONTH('LookUp - Calendar'[Date], 0) = EDATE(EOMONTH(_date, 0), -1)
    &&DAY('LookUp - Calendar'[Date]) <= DAY(_date)
    )

    )
    RETURN _result

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    7 months ago

    cheid 

     

    Last MTD Orders = 
    CALCULATE(
    DISTINCTCOUNT('Repair Orders'[Order Number]),
    DATESMTD(
    DATEADD('LookUp - Calendar'[Date], -1, MONTH)
    ),
    'LookUp - Calendar'[Date] <= TODAY()
    )

    Added TODAY() filter matches current day MTD period. Returns prev month same-day count.

     

    If this answer helped, please click 👍 or Accept as Solution.
    -Kedar
    LinkedIn: https://www.linkedin.com/in/kedar-pande