cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
damomac85 Frequent Visitor
Frequent Visitor

Cumulative formula issue

I have a budget , an actuals and a date table which i am using to show a monthly Cumulative line chart for monthly budget vs actual data.

 

i have added the the follwing measure for a budget Cumulative value which works great as i have a value for each month :-

 

Cumulative Budget =
CALCULATE(SUM('ExcelBudget'[Budget]), Filter(ALL('Date'), 'Date'[Month] <= MAX('ExcelBudget'[Month])))

 

I have the same formula for the actuals cummulative value :- 

 

Cumulative Net =
CALCULATE(SUM('tbFactProspectWon'[NetPrice]), Filter(ALL('Date'), 'Date'[Month] <= MAX('tbFactProspectWon'[DatePaidMonth])))

 

the problem i am having is that if there are no actual values for a given month, no results are showing :-

 

Month         Budget                Budget Cumulative              Actual             Actual Cumulative 

1                  1000                    1000                                     2000                2000

2                  1000                    2000                                     1500                3500

3                  1000                    3000                                                             

4                  1000                    4000                                     1500                5000

5                  1000                    5000                                     1000                6000

6                  1000                    6000                                     1000                7000

7                  1000                    7000                                     1000                8000

 

 

 how can i force the records to show as follows :-

 

Month         Budget                Budget Cumulative              Actual             Actual Cumulative 

1                  1000                    1000                                     2000                2000

2                  1000                    2000                                     1500                3500

3                  1000                    3000                                                             3500

4                  1000                    4000                                     1500                5000

5                  1000                    5000                                     1000                6000

6                  1000                    6000                                     1000                7000

7                  1000                    7000                                     1000                8000

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Cumulative formula issue

@damomac85

 

Please try with following measure formula.

 

Cumulative Net = 
CALCULATE (
    SUM ( 'tbFactProspectWon'[NetPrice] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Month] <= MAX ( 'Date'[Month] ) )
)

Cumulative formula issue_1.jpg

 

Best Regards,

Herbert

View solution in original post

2 REPLIES 2
v-haibl-msft Super Contributor
Super Contributor

Re: Cumulative formula issue

@damomac85

 

Please try with following measure formula.

 

Cumulative Net = 
CALCULATE (
    SUM ( 'tbFactProspectWon'[NetPrice] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Month] <= MAX ( 'Date'[Month] ) )
)

Cumulative formula issue_1.jpg

 

Best Regards,

Herbert

View solution in original post

damomac85 Frequent Visitor
Frequent Visitor

Re: Cumulative formula issue

great thanks @v-haibl-msft

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 59 members 1,232 guests
Please welcome our newest community members: