cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

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

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

1 ACCEPTED SOLUTION
Community Champion

@Dayna hey, check this great video for reference:

3 REPLIES 3
Helper V

Great video, thanks!!

Community Champion

@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 🙂

Community Champion

@Dayna hey, check this great video for reference: