DAX - Cumulation based on division and previous filter contexts

[naelske_cronos]

Hello,

I don't have any report to share but I will try to make it clear. I want to cumulate the ratio based on daynumbers like in the image below. It is about how productive machines are and it's about calculating the target based on the machine hours of last year and using ratio's. This based on a few measures.

_AverageMachineHoursLY =

VAR selected_date =

    LOOKUPVALUE (

        'DIM_Date_Disconnected'[Date],

        'DIM_Date_Disconnected'[DateID], SELECTEDVALUE ( DIM_Date[DateID] )

    )

RETURN

    CALCULATE (

        AVERAGEX (

            FILTER (

                DIM_Date,

                YEAR ( [Date] )

                    = YEAR ( selected_date ) - 1

                    && NOT ( [DayOfWeekNumber] IN { 6, 7 } )

            ),

            CALCULATE ( SUM ( FACT_Productivity[MHOURS] ) )

        ),

        REMOVEFILTERS ( DIM_Date[#Yesterday] ),

        REMOVEFILTERS ( DIM_Machine ),

        REMOVEFILTERS ( DIM_Department )

    )

The user selects a day like 01/01/2024 which is Monday. The measure _AverageMachineHoursLY will show the average of machine hours made last year on the current selected day thus monday ignoring all the rest of the filters (see the remove filters). The following measure _AverageMachineHoursLYTuesday will show the same but for Tuesday as this day is a fixed one to calculate the ratio's afterwards.

_AverageMachineHoursLYTuesday =

VAR selected_date =

    LOOKUPVALUE (

        'DIM_Date_Disconnected'[Date],

        'DIM_Date_Disconnected'[DateID], SELECTEDVALUE ( DIM_Date[DateID] )

    )

RETURN

    IF (

        SELECTEDVALUE ( DIM_Date[DayOfWeekNumber] ) < 6,

        CALCULATE (

            AVERAGEX (

                FILTER (

                    ALL ( DIM_Date[Date], DIM_Date[DayOfWeekNumber] ),

                    YEAR ( [Date] )

                        = YEAR ( selected_date ) - 1

                        && [DayOfWeekNumber] = 2

                ),

                CALCULATE ( SUM ( FACT_Productivity[MHOURS] ) )

            ),

            REMOVEFILTERS ( DIM_Date[#Yesterday] ),

            REMOVEFILTERS ( DIM_Machine ),

            REMOVEFILTERS ( DIM_Department )

        )

    )

The measure _Ratio will divide the average of the selected day by the average of tuesday. As I stated tuesday is a fixed one and has ratio 1.

_Ratio =

DIVIDE ( [_AverageMachineHoursLY], [_AverageMachineHoursLYTuesday] )

 
What I want is to have a cumulation of those ratio's. Suppose I select 04/01/2024 which is Thursday, then the ratio's should be summed up for monday + tuesday + wednesday + thursday. I worked with a disconnected date table so it will check which day has been selected by the user in the slicer and then check for the average machines hours of the year prior the selected date. I couldn't not use the main date table otherwise the filters will not work as I want it.

I started with the cumulation measure but it is not adding up:

_RatioCumulation =

VAR CurrentPeriod =

    MAX ( DIM_Date[DayOfWeekNumber] )

RETURN

    CALCULATE (

        SUMX (

            FILTER (

                ALL ( DIM_Date[DayOfWeekNumber] ),

                DIM_Date[DayOfWeekNumber] <= CurrentPeriod

            ),

            CALCULATE ( [_Ratio] )

        )

    )

If someone could help me with this, I'd be gratefull. Thank you

[naelske_cronos]

Hello,

In addition, this measure gives me the correct result:

_RatioCumulation =
VAR selected_date =
LOOKUPVALUE (
'DIM_Date_Disconnected'[Date],
'DIM_Date_Disconnected'[DateID], SELECTEDVALUE ( DIM_Date[DateID] )
)
VAR CurrentDayNumber =
MAX ( DIM_Date[DayOfWeekNumber] )
RETURN
SUMX (
FILTER (
ALL ( DIM_Date[DayOfWeekNumber] ),
DIM_Date[DayOfWeekNumber] <= CurrentDayNumber
),
DIVIDE (
CALCULATE (
AVERAGEX (
FILTER (
DIM_Date,
YEAR ( [Date] )
= YEAR ( selected_date ) - 1
&& NOT ( [DayOfWeekNumber] IN { 6, 7 } )
),
CALCULATE ( SUM ( FACT_Productivity[MHOURS] ) )
),
REMOVEFILTERS ( DIM_Date[#Yesterday] ),
REMOVEFILTERS ( DIM_Machine ),
REMOVEFILTERS ( DIM_Department )
),
IF (
SELECTEDVALUE ( DIM_Date[DayOfWeekNumber] ) < 6,
CALCULATE (
AVERAGEX (
FILTER (
ALL ( DIM_Date[Date], DIM_Date[DayOfWeekNumber] ),
YEAR ( [Date] )
= YEAR ( selected_date ) - 1
&& [DayOfWeekNumber] = 2
),
CALCULATE ( SUM ( FACT_Productivity[MHOURS] ) )
),
REMOVEFILTERS ( DIM_Date[#Yesterday] ),
REMOVEFILTERS ( DIM_Machine ),
REMOVEFILTERS ( DIM_Department )
)
)
)
)

But it seems such overkill to copy/paste the same measure I used before. Referencing them should be much easier if possible?


Kind regards

[Anonymous]

Hi @naelske_cronos 

You're right, it does seem a bit tedious to paste the same measures over and over again. We can simplify the code by referencing previously defined measures. Here is a simplified example:

_RatioCumulation = 
VAR selected_date = LOOKUPVALUE ( 'DIM_Date_Disconnected'[Date], 'DIM_Date_Disconnected'[DateID], SELECTEDVALUE ( DIM_Date[DateID] ) )
VAR CurrentDayNumber = MAX ( DIM_Date[DayOfWeekNumber] )
RETURN
SUMX (
    FILTER (
        ALL ( DIM_Date[DayOfWeekNumber] ),
        DIM_Date[DayOfWeekNumber] <= CurrentDayNumber
    ),
    [_Ratio]
)

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