DAX code amendment

[Ben1981]

Can anyone help me amend this snippet of DAX? It works great except I need it to have a fixed date period, so I want the date range to have a fixed min month regardless of any date filter applied.

 FILTER (

        SELECTCOLUMNS (

            ALLSELECTED ( 'Dim Date Table'[Month]),

            "Known[X]", 'Dim Date Table'[Month],

            "Known[Y]", [_Sum_measure]

        ),

        AND (

            NOT ( ISBLANK ( Known[X] ) ),

            NOT ( ISBLANK ( Known[Y] ) )

        )

[Ben1981]

Sorry for the delay in response but I have been working on another peice of work and only just got back to this. 

The full DAX measure is below, it's a linear regression calculation that I grabbed from these forums and works great. 
However the history of actuals from the knownY variable that the forecast is based upon is over a year long and I want a table visual that only shows the last 6 months, which means this forecast calculation adjusts for that time period.

I want the forecast figures to remain static based on the entire history if possible and allow me to just show those last 6 month figures.

_Forecast =

VAR Known =

    FILTER (

        SELECTCOLUMNS (

            ALLSELECTED ( 'Dim Date Table'[Month]),

            "Known[X]", 'Dim Date Table'[Month],

            "Known[Y]", [_sum__measure]

        ),

        AND (

            NOT ( ISBLANK ( Known[X] ) ),

            NOT ( ISBLANK ( Known[Y] ) )

        )

    )  

VAR Count_Items =

    COUNTROWS ( Known )

VAR Sum_X =

    SUMX ( Known, Known[X] )

VAR Sum_X2 =

    SUMX ( Known, Known[X] ^ 2 )

VAR Sum_Y =

    SUMX ( Known, Known[Y] )

VAR Sum_XY =

    SUMX ( Known, Known[X] * Known[Y] )

VAR Average_X =

    AVERAGEX ( Known, Known[X] )

VAR Average_Y =

    AVERAGEX ( Known, Known[Y] )

VAR Slope =

    DIVIDE (

        Count_Items * Sum_XY - Sum_X * Sum_Y,

        Count_Items * Sum_X2 - Sum_X ^ 2

    )

VAR Intercept =

    Average_Y - Slope * Average_X

RETURN

    SUMX (

        DISTINCT ('Dim Date Table'[Month]),

        Intercept + Slope * 'Dim Date Table'[Month]

    )

[Anonymous]

Hi @Ben1981 ,

Thanks for your reply.

To achieve your goal of displaying only the last 6 months of data in a table visual while keeping the forecast figures based on the entire history, you can create a DAX measure to filter the visual without affecting the forecast calculation. Here's how you can do it:

1. Use your original measure for the forecast calculation.
2. Use a separate measure to filter the table visual to show only the last 6 months.

Below is the measure to Filter the Last 6 Months. This measure will be used to filter the table visual.

Last6Months = 
VAR _LastDate = TODAY()
VAR _FirstDate = EDATE(_LastDate, -6)
RETURN
IF (
        MAX('Dim Date Table'[Date]) >= _FirstDate && MAX('Dim Date Table'[Date]) <= _LastDate,
        1,
        0
    )

In your table visual, add a visual-level filter using the _Last6Months measure and set it to show only rows where the value is 1.

This way, your forecast calculation remains based on the entire history, but your table visual will only display the last 6 months of data.

Let me know if this helps or if you need further assistance!

Best Regards,

Stephen Tao

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

[Ben1981]

Hi,

Thanks for the help but this doesn't work, it filters my visual as you have said but the forecast still re-calculates based on the visual date period.

[Anonymous]

Hi @Ben1981 ,

Does vojtechsima's reply help you?

In addition, if you want to make the fixed date period dynamic. You can create a stand-alone date table that has no relational connections to other tables.

Then put the dates field from date table into a slicer, also modify your measure as:

FILTER (
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Dim Date Table'[Month] ),
            "Known[X]", 'Dim Date Table'[Month],
            "Known[Y]", [_Sum_measure]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    ),
    'Dim Date Table'[Month] >MIN('DATETABLE'[DATE])&& 'Dim Date Table'[Month] < MAX('DATETABLE'[DATE])
)

After this, when you select a date range in the date slicer, the metric will also return results within that date range.

Best Regards,

Stephen Tao

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

[Ben1981]

For both methods, I get an error on this bit of code: 

'Dim Date Table'[Month] >= DATE(2022, 1, 1)

Or

'Dim Date Table'[Month] >MIN('DATETABLE'[DATE])&& 'Dim Date Table'[Month] < MAX('DATETABLE'[DATE])

A single value for column 'Month' in table 'Dim Date Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Anonymous]

Well, I need some dummy sample data for reference.

Can you provide a little bit of that, it would be very helpful to solve the problem.

And what does your full formula look like? Because I only see the FILTER part.

Best Regards,

Stephen Tao

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

[vojtechsima]

Hello, @Ben1981 ,

bit confused about your measure but if you just need to force some starting date then, add this to end:

'Dim Date Table'[Month] >= DATE(2022, 1, 1)

so whole code (wrapping everything inside another FIlTER, because it's different table so it may cause issues putting it to the first one)

FILTER (
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Dim Date Table'[Month] ),
            "Known[X]", 'Dim Date Table'[Month],
            "Known[Y]", [_Sum_measure]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    ),
    'Dim Date Table'[Month] >= DATE(2022, 1, 1)
)