Previous Month's MTD calculation

[cheid]

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

[Kedar_Pande]

@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

[FreemanZ]

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[samratpbi · Accepted solution]

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]

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.