Calculate the MTD and YTD for the corresponding dax.

[Anonymous]

I am having a table where there are duplication of column code and its corresponding productioncount . So I wrote the DAX to take average of production of each code and sum it up to get the total production of day. 

NTest = 
var x = SUMX(SUMMARIZE(WASTAGE,(WASTAGE[CODE]), "SUMM", sum(WASTAGE[PRODUCTION])), [SUMM])
var y = count(WASTAGE[PRODUCTION])
return
x/y

NTest1 = 
VAR a = VALUES(WASTAGE[CODE] )
VAR Result = SUMX(a,[NTest])
RETURN 
IF(HASONEFILTER(WASTAGE[CODE]), [NTest], Result)

Now I need to calculate MTD and YTD using the same logic i.e, when i select a date, it needs to find the average production of each code, sum it up for all the days till the date selected. Any help is appreciated.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

MTD

MTD Average Production = 
VAR SelectedDate = MAX(WASTAGE[Date]) 
VAR MTDRange = DATESMTD(WASTAGE[Date])
RETURN
CALCULATE(
    [NTest1],
    MTDRange,
    FILTER(ALLSELECTED(WASTAGE), WASTAGE[Date] <= SelectedDate)
)

YTD

YTD Average Production = 
VAR SelectedDate = MAX(WASTAGE[Date])
VAR YTDRange = DATESYTD(WASTAGE[Date])
RETURN
CALCULATE(
    [NTest1],
    YTDRange,
    FILTER(ALLSELECTED(WASTAGE), WASTAGE[Date] <= SelectedDate)
)

Final output

Best regards

Albert He

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