Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mattpar
Frequent Visitor

How to stop showing 'cumulative actual' line values for future weeks.

Hi,

 

Below is my problem.. and thanks in advance for any solutions!

 

Line's below represent cumulative budget vs actual figures for year-week.

 

I am showing future 4 weeks budget forecast in the graph (red line) but I do not want to see a flat cumulative actual (blue) line for the future weeks. Since this is a weekly updated graph I am looking into a moving solution not just a running total up to 2020-31. Also to note my year-week values are stored as text.

 

Currently my runnning total measure is like below. It starts from week 2020-19 and goes up to max week number.

 

Cumulative Actual = CALCULATE([Actual Spent],FILTER('Combined'[Year-Week] >= "2020-19" && 'Combined'[Year-Week] <= MAX('Combined'[Year-Week])))

 

Capture.JPG

 

 

Capture2.JPG

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Mattpar ,

Please update the formula of your measure "Cumulative Actual" as below and check whether it can get your desired result:

Cumulative Actual =
VAR _CumActual =
    CALCULATE (
        [Actual Spent],
        FILTER (
            'Combined',
            'Combined'[Year-Week] >= "2020-19"
                && 'Combined'[Year-Week] <= MAX ( 'Combined'[Year-Week] )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( [Actual Spent] ) ), _CumActual, BLANK () )

Best Regards

Rena

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @Mattpar ,

Please update the formula of your measure "Cumulative Actual" as below and check whether it can get your desired result:

Cumulative Actual =
VAR _CumActual =
    CALCULATE (
        [Actual Spent],
        FILTER (
            'Combined',
            'Combined'[Year-Week] >= "2020-19"
                && 'Combined'[Year-Week] <= MAX ( 'Combined'[Year-Week] )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( [Actual Spent] ) ), _CumActual, BLANK () )

Best Regards

Rena

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

@v-yiruan-msft That is all I needed. Works perfectly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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