Historic and Future data in one measure based on current date, and correct row / column totals?

[Dayna]

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

[Dayna]

Great video, thanks!!

[SpartaBI]

@Dayna my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[SpartaBI · Accepted solution]

@Dayna hey, check this great video for reference:
https://www.youtube.com/watch?v=DKgF-5QHY68

      

Showcase Report – Contoso By SpartaBI