Forum Discussion

loodle's avatar
loodle
Frequent Visitor
4 years ago
Solved

Help with MTD Measure

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

 

Record NumberRepair DateStatus
11/1/2022Closed
23/4/2022Closed
3 Active
43/4/2022Closed
53/18/2022Closed
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]
Need Help
  • NickolajJessen's avatar
    NickolajJessen
    4 years ago

    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


10 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    loodle , Create a date table join with the Repair date

     

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

    • loodle's avatar
      loodle
      Frequent Visitor
      4 years ago

      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?

  • NickolajJessen's avatar
    NickolajJessen
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      loodle
      Frequent Visitor
      4 years ago

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

  • ghoshabhijeet's avatar
    ghoshabhijeet
    Icon for Solution Supplier rankSolution Supplier
    4 years ago

    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 !