topic Re: Linear regression on UNION data in DAX Commands and Tips

Linear regression on UNION data

IvoM — Wed, 05 Feb 2025 11:55:45 GMT

Hey all,
I have been trying to solve this for quite some time now. I would like a measure that calculate the linear regression. But here is a twist:
My data comes from two sources:
Fir source is actual real data from sytem that are calculated like this:

ForecastSixMSumQtyOORECLast3Month = 
VAR _EndDate = EOMONTH(TODAY(), -1)
VAR _StartDate = EOMONTH(_EndDate, -3) + 1
RETURN
SUMX(
SUMMARIZECOLUMNS(
    DimCalendar[YYYY-MM],
    KEEPFILTERS( FILTER( ALL( DimCalendar[Date] ), DimCalendar[Date] >= _StartDate && DimCalendar[Date] <= _EndDate )),
    "SumQtyOOREC", SUM(FactsTransactionOOREC[Qty])
),
IF(ISBLANK([SumQtyOOREC]),0,[SumQtyOOREC])
)

Second data source is forecast that is calculated like this:

ForecastSixMSumQtyNewestBackup =
SUMX(DimCalendar,
    CALCULATE(
        SUM(FactsSixMForecast[QtyPerDay]),
        FactsSixMForecast[BackUpOrder] IN {1,0}
    )
)

Now I have calculated table:

Test2 =
VAR _EndDate1 = EOMONTH(TODAY(), -1)
VAR _StartDate1 = EOMONTH(_EndDate1, -3) + 1
VAR _StartDate2 = EOMONTH(TODAY(), -1) + 1
VAR _EndDate2 = EOMONTH(_StartDate2, 2)
VAR CombinedData = 
    UNION(
        SUMMARIZECOLUMNS(
            DimCalendar[YYYY-MM],
            FILTER(
                ALL(DimCalendar[Date]),
                DimCalendar[Date] >= _StartDate1 && DimCalendar[Date] <= _EndDate1
            ),
            FILTER(
                ALL(DimPartProductFamily),
                DimPartProductFamily[FamilyType] = "Výroba"
            ),
            "Qty", SUM(FactsTransactionOOREC[Qty])
        ),
        SUMMARIZECOLUMNS(
            DimCalendar[YYYY-MM],
            FILTER(
                ALL(DimCalendar[Date]),
                DimCalendar[Date] >= _StartDate2 && DimCalendar[Date] <= _EndDate2
            ),
            FILTER(
                ALL(DimPartProductFamily),
                DimPartProductFamily[FamilyType] = "Výroba"
            ),
            "Qty", [ForecastSixMSumQtyNewestBackup]
        )
    )
RETURN
    ADDCOLUMNS(
        CombinedData,
        "Rank", RANKX(CombinedData, [YYYY-MM], , ASC, DENSE),
        "Date", DATE(LEFT([YYYY-MM], 4), RIGHT([YYYY-MM], 2), 1)
    )

The column date is there only so I can create relationship between this table and DimCalendar[Date]
The result of this calculated table is this:

YYYY-MM	Qty	Rank	Date
2024-11	69634	1	01.11.2024 0:00:00
2024-12	52778	2	01.12.2024 0:00:00
2025-01	90086	3	01.01.2025 0:00:00
2025-02	91059,9999999997	4	01.02.2025 0:00:00
2025-03	99472,3	5	01.03.2025 0:00:00
2025-04	106012,875	6	01.04.2025 0:00:00

Then I have second calculated table:

Test1 =
VAR _EndDate1 = EOMONTH(TODAY(), -1)
VAR _StartDate1 = EOMONTH(_EndDate1, -3) + 1
VAR _StartDate2 = EOMONTH(TODAY(), -1) + 1
VAR _EndDate2 = EOMONTH(_StartDate2, 2)
VAR CombinedData = 
    UNION(
        SUMMARIZECOLUMNS(
            DimCalendar[YYYY-MM],
            FILTER(
                ALL(DimCalendar[Date]),
                DimCalendar[Date] >= _StartDate1 && DimCalendar[Date] <= _EndDate1
            ),
            FILTER(
                ALL(DimPartProductFamily),
                DimPartProductFamily[FamilyType] = "Výroba"
            ),
            "Qty", SUM(FactsTransactionOOREC[Qty])
        ),
        SUMMARIZECOLUMNS(
            DimCalendar[YYYY-MM],
            FILTER(
                ALL(DimCalendar[Date]),
                DimCalendar[Date] >= _StartDate2 && DimCalendar[Date] <= _EndDate2
            ),
            FILTER(
                ALL(DimPartProductFamily),
                DimPartProductFamily[FamilyType] = "Výroba"
            ),
            "Qty", [ForecastSixMSumQtyNewestBackup]
        )
    )
VAR CombinedDataWithRank = 
    ADDCOLUMNS(
        CombinedData,
        "Rank", RANKX(CombinedData, [YYYY-MM], , ASC, DENSE)
    )
RETURN
    LINESTX(CombinedDataWithRank, [Qty], [Rank], TRUE)

The result is this table:

Slope1	Intercept	StandardErrorSlope1	StandardErrorIntercept	CoefficientOfDetermination	StandardError	FStatistic	DegreesOfFreedom	RegressionSumOfSquares	ResidualSumOfSquares
9227,17928571427	52545,4016666667	2666,81558601804	10385,7545758153	0,749555353446732	11156,0899947967	11,9715931446322	4	1489964657,4875	497833375,888011

With all this I have this final calculation:

ForecastSixM-3+3TrendLine =    
    MAX(Test1[Intercept]) + Max(Test1[Slope1]) *MAX(Test2[Rank])

This gives me nice straight line showing the trend:

Since this is quite a lot calculated tables, I wanted to make it bit more simple using one measure. So I come up with this measure:

TestForecastSixM-3+3TrendLine =
VAR _EndDate1 = EOMONTH(TODAY(), -1)
VAR _StartDate1 = EOMONTH(_EndDate1, -3) + 1
VAR _StartDate2 = EOMONTH(TODAY(), -1) + 1
VAR _EndDate2 = EOMONTH(_StartDate2, 2)
VAR CombinedData = 
    UNION(
        SUMMARIZECOLUMNS(
            DimCalendar[YYYY-MM],
            FILTER(
                ALL(DimCalendar[Date]),
                DimCalendar[Date] >= _StartDate1 && DimCalendar[Date] <= _EndDate1
            ),
            FILTER(
                ALL(DimPartProductFamily),
                DimPartProductFamily[FamilyType] = "Výroba"
            ),
            "Qty", SUM(FactsTransactionOOREC[Qty])
        ),
        SUMMARIZECOLUMNS(
            DimCalendar[YYYY-MM],
            FILTER(
                ALL(DimCalendar[Date]),
                DimCalendar[Date] >= _StartDate2 && DimCalendar[Date] <= _EndDate2
            ),
            FILTER(
                ALL(DimPartProductFamily),
                DimPartProductFamily[FamilyType] = "Výroba"
            ),
            "Qty", [ForecastSixMSumQtyNewestBackup]
        )
    )
VAR CombinedDataWithRank = 
    ADDCOLUMNS(
        CombinedData,
        "Rank", RANKX(CombinedData, [YYYY-MM], , ASC, DENSE)
    )
VAR RegressionResult = 
    LINESTX(
        CombinedDataWithRank,
        [Qty], [Rank], TRUE
    )
VAR Intercept = MAXX(RegressionResult, [Intercept])
VAR Slope = MAXX(RegressionResult, [Slope1])
VAR CurrentRank = MAXX(CombinedDataWithRank, [Rank])
RETURN
    Intercept + Slope * CurrentRank

The only problem is that I can't remove the context filter. So if I use this in table visual or my chart the result is that the LINESTX is calculated for every line and not for the whole CombinedDataWithRank variable.
So the result is this:

YYYY-MM	ForecastSixM-3+3TrendLine	TestForecastSixM-3+3TrendLine
2024-11	61772,58095	69634
2024-12	70999,76024	52778
2025-01	80226,93952	90086
2025-02	89454,11881	91060
2025-03	98681,2981	99472,3
2025-04	107908,4774	106012,875

If someone could help me to solve this issue that would be great. Or confirm that the aproach with calculated tables is my best option.
Thanks to anyone who replies 😊

Re: Linear regression on UNION data

Anonymous — Thu, 06 Feb 2025 07:00:03 GMT

Hi IvoM ,

Please try using the REMOVEFILTERS function in the measure to remove the context filter.

TestForecastSixM-3+3TrendLine = VAR _EndDate1 = EOMONTH(TODAY(), -1) VAR _StartDate1 = EOMONTH(_EndDate1, -3) + 1 VAR _StartDate2 = EOMONTH(TODAY(), -1) + 1 VAR _EndDate2 = EOMONTH(_StartDate2, 2) VAR CombinedData = UNION( SUMMARIZECOLUMNS( DimCalendar[YYYY-MM], FILTER( ALL(DimCalendar[Date]), DimCalendar[Date] >= _StartDate1 && DimCalendar[Date] <= _EndDate1 ), FILTER( ALL(DimPartProductFamily), DimPartProductFamily[FamilyType] = "Výroba" ), "Qty", SUM(FactsTransactionOOREC[Qty]) ), SUMMARIZECOLUMNS( DimCalendar[YYYY-MM], FILTER( ALL(DimCalendar[Date]), DimCalendar[Date] >= _StartDate2 && DimCalendar[Date] <= _EndDate2 ), FILTER( ALL(DimPartProductFamily), DimPartProductFamily[FamilyType] = "Výroba" ), "Qty", [ForecastSixMSumQtyNewestBackup] ) ) VAR CombinedDataWithRank = ADDCOLUMNS( CombinedData, "Rank", RANKX(CombinedData, [YYYY-MM], , ASC, DENSE) ) VAR AllCombinedDataWithRank = CALCULATETABLE( CombinedDataWithRank, REMOVEFILTERS(DimCalendar), REMOVEFILTERS(DimPartProductFamily) ) VAR RegressionResult = LINESTX( AllCombinedDataWithRank, [Qty], [Rank], TRUE ) VAR Intercept = MAXX(RegressionResult, [Intercept]) VAR Slope = MAXX(RegressionResult, [Slope1]) VAR CurrentRank = MAXX(CombinedDataWithRank, [Rank]) RETURN Intercept + Slope * CurrentRank

Please try it and let me know if it helps, and if it doesn't, please provide a Power BI Desktop file in progress (with sensitive information removed) that fully covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set up public access), SharePoint, or a Github repository, and then share the URL of the file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Linear regression on UNION data

IvoM — Fri, 07 Feb 2025 07:47:08 GMT

Hey Anonymous

Thank you for your quick response! I tried your proposed solution, but unfortunately, it doesn’t change the result of the measure.

The issue seems to be that every measure I try is still being evaluated row by row.

Here is the link to the PBIX file—I’ve removed all unnecessary information. Even though it's a completely new file, the behavior remains the same.
Test1.pbix

Thanks again for your help!

Edit: The table aproach also disable filtering based on Contract, PartNo and PartProductFamily which is not ideal.