Issue with DAX Measures for Forecast Model in Power BI

[T_JF2022]

Hello everyone,

I’m currently building a forecast model in Power BI that relies on multiple fact tables. The model is designed to display the entire year 2024, broken down by individual months (Jan-Dec). I’m using four different tables for this:

1. Monthending (the result of the month-end closing)
2. Orders (all open orders)
3. Invoices (all invoiced orders of the current month)
4. Prod-FC (Production Forecast)

The 2024 timeline should be filled with the revenue from Monthending for all months as long as this dataset contains data. The following month after the last dataset should determine the cumulative value of Orders & Invoices for the current month. The subsequent months should be filled with the Prod-FC.

I’ve divided this calculation into two measures:

1. Determining the current revenue

Rev-AC 4 = 
VAR CurrentPeriod = MONTH(MAX('invoices'[Invoice DATE]))
VAR Customer = SELECTEDVALUE('CustomerGroups'[CustomerSTAT]) 
VAR OrdersREV = CALCULATE(SUM('orders'[Amount]), ALL('Orders'), 'Orders'[OMonth] <= CurrentPeriod, 'orders'[CustomerSTAT] = Customer)
VAR InvoicesREV = CALCULATE(SUM('invoices'[Amount]), ALL('Invoices'), 'invoices'[IMonth] = CurrentPeriod, 'invoices'[CustomerSTAT] = Customer)
VAR TotalOrderREV = CALCULATE(SUM('orders'[Amount]), ALL('Orders'), 'Orders'[OMonth] <= CurrentPeriod)
VAR TotalInvoicesREV = CALCULATE(SUM('invoices'[Amount]), ALL('Invoices'), 'invoices'[IMonth] = CurrentPeriod)
RETURN
IF(CurrentPeriod = 3, IF(HASONEVALUE('CustomerGroups'[CustomerSTAT]), OrdersREV + InvoicesREV, TotalOrderREV + TotalInvoicesREV), BLANK())

1. The total revenue for the whole year with distribution on the timeline

FC_REV 3 = 
VAR ME_REV = CALCULATE(SUM('monthending'[Amount]))
VAR RevAC = [Rev-AC 4]
VAR ProdFc_REV = CALCULATE(SUM('prod-fc'[Amount]))
RETURN
IF(
    NOT(ISBLANK(RevAC)), RevAC,
    IF(
        NOT(ISBLANK(ME_REV)), ME_REV,
        IF(
            NOT(ISBLANK(ProdFc_REV)), ProdFc_REV,
            BLANK()
        )
    )
)

I’m encountering a problem with the second measure (maybe 1st measure also). While the distribution to the individual months in this model works without issues (although it’s not dynamically determined and requires a clear definition of the current month by me), and the column subtotals are also correct, the row total is incorrect. In the row total, the result of the current month is displayed instead of the sum of all individual sums of the months.

Perhaps my DAX approach is too complicated, but I hope someone can shed some light on this issue. Attached you will find the data basis and the PBIX file.

https://drive.google.com/drive/folders/1v16In4_XjfpwSmhnPqd_mKokiDAO-NWB?usp=drive_link 

Thank you in advance for your help!

[JamesFR06 · Accepted solution]

Hi @T_JF2022 

This code should work.

Gobal2 =
VAR MaxDateActuals =
CALCULATE ( MAX ( monthending[Finished Period] ), REMOVEFILTERS () )
VAR moisAct =
MONTH ( MaxDateActuals )
VAR act =
SUMMARIZE (
monthending,
monthending[Amount],
monthending[CustomerSTAT],
monthending[Finished Period],
"Origin", "Actuals"
)
VAR Inv =
SUMMARIZE (
invoices,
invoices[Amount],
invoices[CustomerSTAT],
invoices[Invoice DATE],
"Origin", "Invoice"
)
VAR Ord =
SUMMARIZE (
orders,
orders[Amount],
orders[CustomerSTAT],
orders[Planned DATE],
"Origin", "Invoice"
)
VAR Frcst =
SUMMARIZE (
'prod-fc',
'prod-fc'[Amount],
'prod-fc'[CustomerSTAT],
'prod-fc'[Production Period],
"Origin", "Forecast"
)
VAR base1 =
UNION ( act, Inv )
VAR base2 =
UNION ( Ord, Frcst )
VAR Endingbase =
FILTER (
ADDCOLUMNS (
UNION ( base1, base2 ),
"MoisFinal",
IF (
AND ( MONTH ( monthending[Finished Period] ) <= moisAct, [Origin] = "Actuals" ),
MONTH ( monthending[Finished Period] ),
IF (
AND (
MONTH ( monthending[Finished Period] ) <= moisAct + 1,
[Origin] = "Invoice"
),
moisAct + 1,
IF (
AND (
MONTH ( monthending[Finished Period] ) >= moisAct + 2,
[Origin] = "Forecast"
),
MONTH ( monthending[Finished Period] ),
0
)
)
)
),
[MoisFinal] <> 0
)
VAR res2 =
SUMX ( Endingbase, [Amount] )
RETURN
res2

[T_JF2022]

Insane, it worked 😋

[v-nuoc-msft]

Hi @T_JF2022 

In Power BI, if the row totals for a matrix are incorrect, this is usually because of the way the metric is calculated.

To ensure that the row totals are displayed correctly, you can try the following steps:

Ensure that your metrics are calculated correctly in the detail rows.

I notice that you are using sum for your summation, perhaps you could consider using sumx.

Use the SUMX function to wrap your metric value on top of your metric value to make sure that both the detail rows and the total rows are correct.

Regards,

Nono Chen

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