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