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...
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
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