Help with MTD Measure

[loodle]

Just trying to use the MTD function for the following data:

Record Number	Repair Date	Status
1	1/1/2022	Closed
2	3/4/2022	Closed
3		Active
4	3/4/2022	Closed
5	3/18/2022	Closed
6		Active

I have followed the instructions to create a Date Table, but can't figure out how to get a MTD count of Closed Records. I thought it was be something like this: 

CLOSED MTD =

TOTALMTD(

    DISTINCTCOUNT ([RECORDNUMBER]),

    'DATE'[Date]

[ghoshabhijeet]

@loodle  Does your record table have data for Today? Will be able to help better if you could provide the PBIX file with some sample data. Thanks !

[NickolajJessen]

I'm not sure why you need this one MTD setup?

can't you just make a COUNT and add a relate date filter to the visual saying In this month?

[loodle]

I am trying to create a table that has other columns that I don't want to filter by realtive date. 

[NickolajJessen]

Allright, does something like this work for you?

[loodle]

Gave me the following error:

Error Message:
MdxScript(Model) (21, 13) Calculation error in measure 'LINEINSPECTIONSTLM_EVW'[CLOSED MTD]: A date column containing duplicate dates was specified in the call to function 'TOTALMTD'. This is not supported.

[NickolajJessen · Accepted solution]

Haha, i don't understand these MTD formulas at all 😂

How about something more lowkey like:

This month = 
CALCULATE(
    DISTINCTCOUNT('Tabel (2)'[Record Number])
    ,FILTER('Calendar', 
        'Calendar'[Date] <= EOMONTH(TODAY(),0)
        && 'Calendar'[Date] >= (EOMONTH(TODAY(),-1)+1)

    )
)

Should give you data between 01-06-2022 and 31-06-2022

[loodle]

This seemed to work, thanks. Now I want to add YTD, struglling to understand the difference between EOMONTH and ENDOFMONTH, and the is no EOYEAR. 

[v-chenwuz-msft]

Hi @loodle,

Very happy that your problem was solved. Please mark it as a solution.
About YTD, you can recreate a post so that you can get better help.

EOMONTH

• Specify a start date. Before or after a few months and return the last day of the month. for example a table with date column from 1/1/2022 to 1/13/2022, 1 month after 1/1/2022, result of EOMONTH is 2/28/2022.

ENDOFMONTH 

• get the last date of current context. for example a table with date column from 1/1/2022 to 1/13/2022, then ENDOFMONTH() return 1/13/2022 rather than 1/31/2022

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@loodle , Create a date table join with the Repair date

CALCULATE(DISTINCTCOUNT (Table[RECORDNUMBER]),DATESMTD('Date'[Date]), not(isblank(Table[Repair Date])) )

[loodle]

I joined the date table to the record table by a one to many relationship on the the Repair Date. I am getting a count now, but it is not the correct number if I verify by filtering my data to repair dates of June 2022. Am I missing TODAY somewhere or does DATESMTD already know that?