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