Forum Discussion

cpereyra's avatar
cpereyra
Helper I
6 years ago
Solved

MTD

Hi all,#MTD

 

I'm trying to calculate a measure of MTD using the following DAX. The only problem I have is when trying to visualize if one person doesn't have a date for the current month it will calculate the previous month. How can I make it show zero if the is no date for this month. TIA.

 

Total MTD Funded = CALCULATE(SUM(Merge1[PEC_Work_Load.Total_Loan_amount]),DATESMTD(Merge1[Extra_Fields.Closed Date]))
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello cpereyra 

    You can add a VAR to get the month of TODAY() and add that as a filter to your CALCULATE.

    Total MTD Funded =
VAR _CurrMonth =
    MONTH ( TODAY () )
RETURN
    CALCULATE (
        SUM ( Merge1[PEC_Work_Load.Total_Loan_amount] ),
        DATESMTD ( Merge1[Extra_Fields.Closed Date] ),
        MONTH ( Merge1[Extra_Fields.Closed Date] ) = _CurrMonth
    )

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Hello cpereyra 

    You can add a VAR to get the month of TODAY() and add that as a filter to your CALCULATE.

    Total MTD Funded =
VAR _CurrMonth =
    MONTH ( TODAY () )
RETURN
    CALCULATE (
        SUM ( Merge1[PEC_Work_Load.Total_Loan_amount] ),
        DATESMTD ( Merge1[Extra_Fields.Closed Date] ),
        MONTH ( Merge1[Extra_Fields.Closed Date] ) = _CurrMonth
    )