Measure work correctly for each row but showing blank for total

[loafers]

Hi,

I have a measure where it's put into a matrix with employee field added to rows. It works as expected for each row of employee. However, when it comes to the total at the bottom, it shows as blank.

Measure is below. I believe the issue stems from the fact that the _LatestDate may be later than the _MTD/_QTD/_YTDStartDate. If that is the case i want to show 0 for the employee. I tried adding an IF statement to push a 0 but the total is still showing blank

Measure=
VAR _TimeIntelligence=MINX('Time Intelligence','Time Intelligence'[Time Intelligence ID])

Var _Date=EOMONTH(MAX('Calendar'[Date]),0)

Var _LatestDate=MIN(MINX('Employee ID & Name Dim','Employee ID & Name Dim'[Criteria2]),_Date)

Var _MTDStartDate=EOMONTH(_Date,-1)+1

Var _QTDStartDate=DATE(YEAR(_Date),FLOOR(MONTH(_Date)-1,3)+1,1)

Var _YTDStartDate=EOMONTH(_Date,-MONTH(_Date))+1



Var _Sales = SWITCH(_TimeIntelligence,
                        1,CALCULATE([Sales],'Employee ID & Name Dim'[Criteria1]<>"",'Employee ID & Name Dim'[Termination Date]>_Date,FILTER(ALL('Calendar'),'Calendar'[Date]>=_MTDStartDate&&'Calendar'[Date]<=_LatestDate)),
                        2,CALCULATE([Sales],'Employee ID & Name Dim'[Criteria1]<>"",'Employee ID & Name Dim'[Termination Date]>_Date,FILTER(ALL('Calendar'),'Calendar'[Date]>=_QTDStartDate&&'Calendar'[Date]<=_LatestDate)),
                        3,CALCULATE([Sales],'Employee ID & Name Dim'[Criteria1]<>"",'Employee ID & Name Dim'[Termination Date]>_Date,FILTER(ALL('Calendar'),'Calendar'[Date]>=_YTDStartDate&&'Calendar'[Date]<=_LatestDate))
                        )
Return _Sales
​

How can i fix it so that total shows correctly?

[Anonymous]

Hi @loafers ,

You can then create a new measure to be placed on the matrix visual object instead of the measure.

Measure=
VAR _TimeIntelligence=MINX('Time Intelligence','Time Intelligence'[Time Intelligence ID])

Var _Date=EOMONTH(MAX('Calendar'[Date]),0)

Var _LatestDate=MIN(MINX('Employee ID & Name Dim','Employee ID & Name Dim'[Criteria2]),_Date)

Var _MTDStartDate=EOMONTH(_Date,-1)+1

Var _QTDStartDate=DATE(YEAR(_Date),FLOOR(MONTH(_Date)-1,3)+1,1)

Var _YTDStartDate=EOMONTH(_Date,-MONTH(_Date))+1



Var _Sales = SWITCH(_TimeIntelligence,
                        1,CALCULATE([Sales],'Employee ID & Name Dim'[Criteria1]<>"",'Employee ID & Name Dim'[Termination Date]>_Date,FILTER(ALL('Calendar'),'Calendar'[Date]>=_MTDStartDate&&'Calendar'[Date]<=_LatestDate)),
                        2,CALCULATE([Sales],'Employee ID & Name Dim'[Criteria1]<>"",'Employee ID & Name Dim'[Termination Date]>_Date,FILTER(ALL('Calendar'),'Calendar'[Date]>=_QTDStartDate&&'Calendar'[Date]<=_LatestDate)),
                        3,CALCULATE([Sales],'Employee ID & Name Dim'[Criteria1]<>"",'Employee ID & Name Dim'[Termination Date]>_Date,FILTER(ALL('Calendar'),'Calendar'[Date]>=_YTDStartDate&&'Calendar'[Date]<=_LatestDate))
                        )
Return _Sales
​

New measure=SUMX(VALUES('Table'[employee]),[Measure])

Best Regards,

Neeko Tang

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