CUMMULATIVE DAX - DATE AS A COLUMN HEADER (MATRIX VISUAL)

[princess2018]

HELP!

I need a way to show the cummulative KPI % but Date month as column header. 

Cummulative DAX is not working for me because it is just computing the month selected. How can I tweek my DAX to give the cummultive? 
end goal should be

On Feb:
Site = 7 (Site of January = 4 + Site of Feb = 3)
KPI % = KPI % of January + KPI % of February

THANKS FOR YOUR HELP!😣


Here is my Dax: 

00 **bleep** KPI Score =

Var AveKPI_1 = AVERAGE('KPI Master Tracker'[(CDM) H&S Compliance KPI 1])
Var AveKPI_2 = AVERAGE('KPI Master Tracker'[(CM) Commercial Submissions KPI 2])
Var AveKPI_3 = AVERAGE('KPI Master Tracker'[(PM) Delivery Services KPI 3])
Var AveKPI_4 = AVERAGE('KPI Master Tracker'[(CM) Commercial Performance KPI 4])
Var AveKPI_5 = AVERAGE('KPI Master Tracker'[(PM) Snagging KPI 5])
Var AveKPI_6 = AVERAGE('KPI Master Tracker'[(PM) Supplier Management KPI 6])
Var AveKPI_7 = AVERAGE('KPI Master Tracker'[(PM) Customer Satisfaction KPI 7])
Var AveKPI_8 = AVERAGE('KPI Master Tracker'[(PM) Communication KPI 8])
Var AveKPI_9 = AVERAGE('KPI Master Tracker'[(PM) Values and Behaviours KPI 9])
Var AveKPI_10 = AVERAGE('KPI Master Tracker'[(PM) Policy Compliance KPI 10])

Return
SWITCH(
TRUE(),
[00 Count_Projects] = "-",
"-",
CALCULATE(
ROUND(
(
((AveKPI_1 + AveKPI_2 + AveKPI_3 + AveKPI_4 + AveKPI_5 + AveKPI_6 + AveKPI_7) / 105) * 0.75 +
((AveKPI_8 + AveKPI_9 + AveKPI_10) / 45) * 0.25
),
2),
FILTER(
ALLSELECTED('Date'[MonthNameLong]),
'Date'[MonthNameLong] <= MAX('Date'[MonthNameLong])
)
)
)

[amitchandak]

@princess2018 , if you have date table and date in the table. Always prefer to use date in such cases. Grouping of the month should take care of rest.

Not if you do not have the year in context/filter result can be a bit different

FILTER(
ALLSELECTED('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])
)

[princess2018]

its just the same as I did.. and still not giving the cummulative

[Greg_Deckler]

@princess2018 If you can post sample data as text that would be extremely helpful. Looking at your formula, I am concerned about your <= with the month's long name, I don't think that will sort properly, you will need to use a month number for that. And I think you need an ALL instead of ALL selected.

00 **bleep** KPI Score =

Var AveKPI_1 = AVERAGE('KPI Master Tracker'[(CDM) H&S Compliance KPI 1])
Var AveKPI_2 = AVERAGE('KPI Master Tracker'[(CM) Commercial Submissions KPI 2])
Var AveKPI_3 = AVERAGE('KPI Master Tracker'[(PM) Delivery Services KPI 3])
Var AveKPI_4 = AVERAGE('KPI Master Tracker'[(CM) Commercial Performance KPI 4])
Var AveKPI_5 = AVERAGE('KPI Master Tracker'[(PM) Snagging KPI 5])
Var AveKPI_6 = AVERAGE('KPI Master Tracker'[(PM) Supplier Management KPI 6])
Var AveKPI_7 = AVERAGE('KPI Master Tracker'[(PM) Customer Satisfaction KPI 7])
Var AveKPI_8 = AVERAGE('KPI Master Tracker'[(PM) Communication KPI 8])
Var AveKPI_9 = AVERAGE('KPI Master Tracker'[(PM) Values and Behaviours KPI 9])
Var AveKPI_10 = AVERAGE('KPI Master Tracker'[(PM) Policy Compliance KPI 10])
Var MonthName = MAX('Date'[MonthNameLong])
Var DateMax = MAX('Date'[Date])
Var MonthNum = MONTH(DateMax)
Return
SWITCH(
TRUE(),
[00 Count_Projects] = "-",
"-",
CALCULATE(
ROUND(
(
((AveKPI_1 + AveKPI_2 + AveKPI_3 + AveKPI_4 + AveKPI_5 + AveKPI_6 + AveKPI_7) / 105) * 0.75 +
((AveKPI_8 + AveKPI_9 + AveKPI_10) / 45) * 0.25
),
2),
FILTER(
ALL('Date'[MonthNameLong]),
MONTH('Date'[Date])<=MonthNum
)
)
)

[princess2018]

00 **bleep** KPI Score =

Var AveKPI_1 = AVERAGE('KPI Master Tracker'[(CDM) H&S Compliance KPI 1])

Var AveKPI_2 = AVERAGE('KPI Master Tracker'[(CM) Commercial Submissions KPI 2])

Var AveKPI_3 = AVERAGE('KPI Master Tracker'[(PM) Delivery Services KPI 3])

Var AveKPI_4 = AVERAGE('KPI Master Tracker'[(CM) Commercial Performance KPI 4])

Var AveKPI_5 = AVERAGE('KPI Master Tracker'[(PM) Snagging KPI 5])

Var AveKPI_6 = AVERAGE('KPI Master Tracker'[(PM) Supplier Management KPI 6])

Var AveKPI_7 = AVERAGE('KPI Master Tracker'[(PM) Customer Satisfaction KPI 7])

Var AveKPI_8 = AVERAGE('KPI Master Tracker'[(PM) Communication KPI 8])

Var AveKPI_9 = AVERAGE('KPI Master Tracker'[(PM) Values and Behaviours KPI 9])

Var AveKPI_10 = AVERAGE('KPI Master Tracker'[(PM) Policy Compliance KPI 10])

Return

SWITCH(

TRUE(),

[00 Count_Projects] = "-",

"-",

CALCULATE(

ROUND(

(

((AveKPI_1 + AveKPI_2 + AveKPI_3 + AveKPI_4 + AveKPI_5 + AveKPI_6 + AveKPI_7) / 105) * 0.75 +

((AveKPI_8 + AveKPI_9 + AveKPI_10) / 45) * 0.25

),

2),

FILTER(there is an error in this.. there is no month number appearing)

ALL('Date'[MonthNameLong]),

MONTH('Date'[Date] <= '

)

)

))

[v-yingjl · Accepted solution]

Hi @princess2018 ,

I have noticed that it seems like you write the comparison expression in the month() function so that you cannot quote other functions.

Maybe the dax will be like this:

A =
VAR AveKPI_1 =
    AVERAGE ( 'KPI Master Tracker'[(CDM) H&S Compliance KPI 1] )
VAR AveKPI_2 =
    AVERAGE ( 'KPI Master Tracker'[(CM) Commercial Submissions KPI 2] )
VAR AveKPI_3 =
    AVERAGE ( 'KPI Master Tracker'[(PM) Delivery Services KPI 3] )
VAR AveKPI_4 =
    AVERAGE ( 'KPI Master Tracker'[(CM) Commercial Performance KPI 4] )
VAR AveKPI_5 =
    AVERAGE ( 'KPI Master Tracker'[(PM) Snagging KPI 5] )
VAR AveKPI_6 =
    AVERAGE ( 'KPI Master Tracker'[(PM) Supplier Management KPI 6] )
VAR AveKPI_7 =
    AVERAGE ( 'KPI Master Tracker'[(PM) Customer Satisfaction KPI 7] )
VAR AveKPI_8 =
    AVERAGE ( 'KPI Master Tracker'[(PM) Communication KPI 8] )
VAR AveKPI_9 =
    AVERAGE ( 'KPI Master Tracker'[(PM) Values and Behaviours KPI 9] )
VAR AveKPI_10 =
    AVERAGE ( 'KPI Master Tracker'[(PM) Policy Compliance KPI 10] )
RETURN
    SWITCH (
        TRUE (),
        [00 Count_Projects] = "-", "-",
        CALCULATE (
            ROUND (
                 ( ( ( AveKPI_1 + AveKPI_2 + AveKPI_3 + AveKPI_4 + AveKPI_5 + AveKPI_6 + AveKPI_7 ) / 105 ) * 0.75 + ( ( AveKPI_8 + AveKPI_9 + AveKPI_10 ) / 45 ) * 0.25 ),
                2
            ),
            FILTER (
                ALL ( 'Date'[MonthNameLong] ),
                MONTH ( 'Date'[Date] ) <= MAX ( MONTH ( 'Date'[Date] ) )
            )
        )
    )

Best Regards,
Yingjie Li

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

[princess2018]

[v-yingjl]

Hi @princess2018 ,
Try to change the reference order of max() and month(), month() cannot be quoted in max()

Best Regards,
Yingjie Li

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