Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Dayna
Helper V
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
SpartaBI
Community Champion
Community Champion

3 REPLIES 3
Dayna
Helper V
Helper V

Great video, thanks!!

SpartaBI
Community Champion
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 🙂

SpartaBI
Community Champion
Community Champion

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.