Tableau expression into DAX

[Anonymous]

Hi,

I have a Tableau expression that I would like to convert into DAX.

PREVIOUS_VALUE(0)+

MIN(
WINDOW_AVG(SUM([values]),-365-[Days Average],-365+[Days Average]),
WINDOW_AVG(SUM([values]),-730-[Days Average],-730+[Days Average])
)

[Anonymous]

Hi, @MAwwad ,

1) In the first part of the query I get the following error message:
"The first argument of EARLIER/EARLIEST is not a valid column reference in the earlier row context.", - related to this row, MAX('Table'[Date]) = EARLIER(MAX('Table'[Date])) .

2) Also, I am not sure whwther WINDOWAVG function is avaialble, unless it is possible to combine WINDOW and AVERAGE somehow?

[MAwwad]

Hello!

Regarding your first question, the error message suggests that the column reference 'Table'[Date] is not valid in the context of the EARLIER function. This can happen if the EARLIER function is being used in a calculated column, and the column reference is not part of the column being calculated.

To resolve this issue, you can try using a measure instead of a calculated column, which allows you to refer to columns outside of the current row context. You can define a measure using the CALCULATE function, and then use the MAXX function to calculate the maximum date within the current filter context. Here's an example:

 

Max Date = CALCULATE ( MAXX ( 'Table', 'Table'[Date] ), FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ) )

This measure calculates the maximum date for each category in the 'Table' table, by filtering the table to only include rows with the same category as the current row context.

Regarding your second question, the WINDOWAVG function is not a built-in function in DAX. However, you can use the AVERAGEX function along with a FILTER function to calculate a moving average over a specified window of rows. Here's an example:

 

Moving Avg = AVERAGEX ( FILTER ( 'Table', 'Table'[Date] >= EARLIER ( 'Table'[Date] ) - 6 && 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ), 'Table'[Value] )

This measure calculates a 7-day moving average for the 'Value' column in the 'Table' table, by filtering the table to include only rows with a date within the last 7 days, relative to the current row context.

I hope this helps! Let me know if you have any further questions.

[MAwwad]

Cumulative Rolling Average =

VAR PrevValue =

    CALCULATE(

        SUM([values]),

        FILTER(

            ALLSELECTED(),

            MAX('Table'[Date]) = EARLIER(MAX('Table'[Date]))

        )

    )

RETURN

    PrevValue +

    MINX(

        {

            CALCULATE(

                WINDOWAVG(SUM([values]), -365 - [Days Average], -365 + [Days Average])

            ),

            CALCULATE(

                WINDOWAVG(SUM([values]), -730 - [Days Average], -730 + [Days Average])

            )

        },

        [Cumulative Rolling Average]

    )