Forum Discussion

AThakur1's avatar
AThakur1
Frequent Visitor
1 year ago
Solved

Issue with TotalMTD Calculation.

Hi, I am trying to calcuate MTD Consumption & using below dax.    MTDConsumption = TOTALMTD(SUM(Consumption[Qty Consumed]),Consumption[Consumption Date])   In below image selected item was last ...
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    Hi AThakur1 ,

    Try the below DAX,

    MTD Consumption =
    var_CurrentMonth = MONTH(TODAY())
    var_CurrentYear = YEAR(TODAY())
    var_Hasdatathismonth =
    CALCULATE(COUNTROWS(Consumption),
    FILTER(
    Consumption,
    MONTH(Consumption[Consumption Date]) = _CurrentMonth &&
    YEAR(Consumption[Consumption Date]) = _CurrentYear
    )
    ) > 0
    RETURN
    IF(Hasdatathismonth, TOTALMTD(SUM(Consumption[Qty Consumed]), 'Date'[Date]),BLANK()
    It checks if the item has any consumption in April, and only then it shows MTD otherwise shows blank.

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

    Thanks and Regards,
    Chaithra E.

     

     

AThakur1's avatar
AThakur1
Frequent Visitor
1 year ago

(Reposting.. not able to see my previous reply).

 

Thank you for the reply.

 

Below it the desired output. 

 

Tried both dax & they are calculating sum from prevoius months(whichever last date/month available) for highlited yellow items. As data for these items not avilabel for the month of Apr'25 in the dataset. 

 

Its same for MTD & YTD results, they calculating result considering last available date, if data for current month/year not available. However, total for both YTD/MTD is correct.

Data is coming from three table (item number is comman in all tables), 

1-Item master

2-Stock.

3-consumption. 

 

Have also tried creating calander & conneting date but same result. 

MTD Consumption = TOTALMTD(SUM(Consumption[Qty Consumed]),'Date'[Date])