Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
princess2018
Frequent Visitor

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

 

HELP!

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

Screenshot 2020-09-11 213252.png
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])
)
)
)

1 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.

dax.png

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.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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])
)

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

 

Greg_Deckler
Super User
Super User

@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
)
)
)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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] <= '
)
)
))

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.

dax.png

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.

Screenshot 2020-09-24 125732.png

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

max().png

month().png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.