Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎01-24-2017
Accepted Solution

Cumulative 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


Accepted Solutions
Super Contributor
Posts: 670
Registered: ‎06-03-2016

Re: Cumulative issue

Cool my dear friend,

 

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

 

Ya , i agree with you it is working great . 

Reason :

Bec it have the continuos month value on ExcelBudget this table am i right ?

 

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

 

Reason : 

In this table tbFactProspectWon u don't have continuos value on month am i right ?

 

Solution :

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

 

Try this it will help u.

 

let me know if it is not solve your problem.

 

View solution in original post


All Replies
Super Contributor
Posts: 670
Registered: ‎06-03-2016

Re: Cumulative issue

Cool my dear friend,

 

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

 

Ya , i agree with you it is working great . 

Reason :

Bec it have the continuos month value on ExcelBudget this table am i right ?

 

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

 

Reason : 

In this table tbFactProspectWon u don't have continuos value on month am i right ?

 

Solution :

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

 

Try this it will help u.

 

let me know if it is not solve your problem.

 

Frequent Visitor
Posts: 4
Registered: ‎01-24-2017

Re: Cumulative issue

brilliant thanks @Baskar Smiley Happy