Solved: Historic and Future data in one measure based on c...

Dayna · ‎06-22-2022

Hello,

I have a calculation that is showing the historic value or the future value based on the current month, this is also giving me correct row totals. However, the column total is not correct when summing across all the months.

Is there a different way to achieve what I've done? I think it's the 'max' in the first calculation that is causing the problems when aggregated over the entire time period.

Here is my current measure:

VAR ForecastValue =
    IF (
        MAX ( 'Calendar'[YearWeekNr] ) < [Today (Week)],
        [Sales (MU)],
        [Forecast Quantity (MU)]
    ) + 0
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[YearWeekNr] ),
        ForecastValue,
if( HASONEVALUE('Product'[pt_part]), SUMX ( summarize ('ISS-S0 & RCT-SOR & ORD-SO - 600 (Transactions)','Product'[pt_part],'Calendar'[YearWeekNr]), ForecastValue ),
        SUMX ( VALUES ( 'Calendar'[YearWeekNr] ), ForecastValue )
    ))

Many thanks for any help and assistance you can offer.

Kind Regards,

Dayna