Cumulative progress chart [Model attached]

[carlenb]

Hi,

See model with test data here:

https://drive.google.com/file/d/1S4G3rC5-SAothJEkQBq2DhXRtWXrM9a3/view?usp=drivesdk

Outout: I want to create the following graph

Test data used in model 

Contract scheduled done	Reschedule 1	Reschedule 2	Status	Contract done
2023-04-25			Done	2023-04-25
2023-05-15			Done	2023-06-15
2024-01-03	2024-03-01	2024-04-05	Delayed
			Status missing
2024-04-03			Progressing
2024-06-03			Delayed
2024-08-01			Progressing

Variables

Actual

• The cumulative sum of "Contract done" when the status column is "Done". 
• It should only show up until Today's date 
• In the above example, the cumulative sum today would be 2. 

Planned

• The cumulative sum of "Contract scheduled done" 
• It should only show from Today's date and forward 
• In the above example, the cumulative sum would be 2 

Forecast

• The cumulative sum of "Reschedule 2", "Reschedule 1" and "Contract scheduled done" 
• Logic should be: If "Reschedule 2" contains data > pick this value. If "Reschedule 2" is emtpy > look at "Reschedule 1". If "Reschedule 1" contains data > pick this value. If "Reschedule 1" is emtpy > pick the value in "Contract scheduled done" 
• Only from Today's date and forward 

[Anonymous · Accepted solution]

Hi @carlenb ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a calculated column.

Column = IF(ISBLANK('Table'[Reschedule 2]),IF(ISBLANK('Table'[Reschedule 1]),[Contract scheduled done],[Reschedule 1]),[Reschedule 2])

(2) We can create measures. 

Measure = 
var _Actual=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),'Table'[Status]="Done" && [Contract scheduled done]<=TODAY()))
var _Planned=CALCULATE(COUNT('Table'[Contract scheduled done]),FILTER(ALLSELECTED('Table'),[Contract scheduled done]>TODAY()))
RETURN IF(MAX('Calendar'[Date])<=TODAY(),_Actual,_Planned)

Measure 2 = 
var _Actual=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),'Table'[Status]="Done" && [Contract scheduled done]<=TODAY()))
var _Forecast=CALCULATE(COUNT('Table'[Column]),FILTER(ALLSELECTED('Table'),[Column]>TODAY()))
RETURN IF(MAX('Calendar'[Date])<=TODAY(),_Actual,_Forecast)

(3)Then the result is as follows.

If the above one can't help you get the desired result, please provide your expected result with backend logic and special examples. Thank you.

Best Regards,

Neeko Tang

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

[Anonymous · Accepted solution]

Hi @carlenb ,

We can create three measures.

Actual = 
var _max_date=CALCULATE(MAX('Table'[Contract done]),FILTER(ALL('Table'),[Status]="Done"))
var _Actual=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),'Table'[Status]="Done" && [Contract done]<=MAX('Calendar'[Date])))
RETURN IF(EOMONTH(MAX('Calendar'[Date]),0)<=EOMONTH(_max_date,0),_Actual,BLANK())

Planned = 
var _Planned=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),[Contract scheduled done]<=MAX('Calendar'[Date])))
RETURN _Planned

Forecast = 
var _max_date=CALCULATE(MAX('Table'[Contract done]),FILTER(ALL('Table'),[Status]="Done"))
var _Forecast=CALCULATE(COUNT('Table'[Column]),FILTER(ALLSELECTED('Table'),[Column]<=MAX('Calendar'[Date])))
RETURN IF(EOMONTH(MAX('Calendar'[Date]),0)>=EOMONTH(_max_date,0),_Forecast,BLANK())

The x-axis places the date field of the date table. The date table cannot have duplicate dates.

There can be only one active relationship between the two tables. If you have more than one relationship between the date table and the fact table, you can use the USERELATIONSHIP() function to activate the inactive relationship.

Best Regards,

Neeko Tang

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

[carlenb]

Thanks a lot @Anonymous , I appreciate the help! I checked this for my use case and realized that there are some modifications needed. I increased the test data a bit (new link to model here) and created an output table and graph (Excel) below to better showcase the logic of the end result. 

Data set: 

Contract scheduled done	Reschedule 1	Reschedule 2	Status	Contract done
2023-04-25			Done	2023-04-25
2023-05-15			Done	2023-06-15
2024-01-03	2024-03-01	2024-06-05	Delayed
2024-02-03	2024-07-01		Delayed
2024-03-01			Done	2024-03-01
2024-04-03			Progressing
2024-06-03			Delayed
2024-08-01			Progressing

Expected output based on data set: 

	Actual	Planned	Forecast
apr-23	1	1
maj-23	1	2
jun-23	2	2
aug-23	2	2
sep-23	2	2
okt-23	2	2
nov-23	2	2
dec-23	2	2
jan-24	2	3
feb-24	2	4
mar-24	3	5	3
apr-24		6	4
maj-24		6	4
jun-24		7	5
jul-24		7	5
aug-24		8	6

End result (Excel graph):

To specify each variable: 

Actual

• Cumulative sum for when column "Status" is "Done"
• No need to use TODAY() function here > can be removed 

Planned

• Cumulative sum of "Contract schedule done"
• No need to use TODAY() function here > can be removed 

Forecast

• The calculated column will remain as the logic here is the same, except one change that it should start the cumulative Forecast count based on the latest "Actual" cumulative value. See example in the output table where "Actual" is 3 in March 2023 - this would be the starting value for Forecast

One more consideration is if USERELATIONSHIP should be used, given Calender will be used on the x-axis and there are multiple dates in Table? 

Appreciate your patience!

[Anonymous · Accepted solution]

Hi @carlenb ,

We can create three measures.

Actual = 
var _max_date=CALCULATE(MAX('Table'[Contract done]),FILTER(ALL('Table'),[Status]="Done"))
var _Actual=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),'Table'[Status]="Done" && [Contract done]<=MAX('Calendar'[Date])))
RETURN IF(EOMONTH(MAX('Calendar'[Date]),0)<=EOMONTH(_max_date,0),_Actual,BLANK())

Planned = 
var _Planned=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),[Contract scheduled done]<=MAX('Calendar'[Date])))
RETURN _Planned

Forecast = 
var _max_date=CALCULATE(MAX('Table'[Contract done]),FILTER(ALL('Table'),[Status]="Done"))
var _Forecast=CALCULATE(COUNT('Table'[Column]),FILTER(ALLSELECTED('Table'),[Column]<=MAX('Calendar'[Date])))
RETURN IF(EOMONTH(MAX('Calendar'[Date]),0)>=EOMONTH(_max_date,0),_Forecast,BLANK())

The x-axis places the date field of the date table. The date table cannot have duplicate dates.

There can be only one active relationship between the two tables. If you have more than one relationship between the date table and the fact table, you can use the USERELATIONSHIP() function to activate the inactive relationship.

Best Regards,

Neeko Tang

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

[carlenb]

Thanks a lot @Anonymous. Could you help me adjust the measures to include USERELATIONSHIP? I've tried but can't quite get it right. 

Again, thanks for your support and patience! 

[Anonymous · Accepted solution]

Hi @carlenb ,

Try them. You can create new measures.

New Actual Measure= CALCULATE (
            [Actual measure],
            USERELATIONSHIP ( FactTable[Date], 'Date'[Date] )
        )

New Planned Measure= CALCULATE (
            [Planned measure],
            USERELATIONSHIP ( FactTable[Date], 'Date'[Date] )
        )

New Forecast Measure= CALCULATE (
            [Forecast measure],
            USERELATIONSHIP ( FactTable[Date], 'Date'[Date] )
        )

Best Regards,

Neeko Tang

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

[Anonymous · Accepted solution]

Hi @carlenb ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a calculated column.

Column = IF(ISBLANK('Table'[Reschedule 2]),IF(ISBLANK('Table'[Reschedule 1]),[Contract scheduled done],[Reschedule 1]),[Reschedule 2])

(2) We can create measures. 

Measure = 
var _Actual=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),'Table'[Status]="Done" && [Contract scheduled done]<=TODAY()))
var _Planned=CALCULATE(COUNT('Table'[Contract scheduled done]),FILTER(ALLSELECTED('Table'),[Contract scheduled done]>TODAY()))
RETURN IF(MAX('Calendar'[Date])<=TODAY(),_Actual,_Planned)

Measure 2 = 
var _Actual=CALCULATE(COUNT('Table'[Status]),FILTER(ALLSELECTED('Table'),'Table'[Status]="Done" && [Contract scheduled done]<=TODAY()))
var _Forecast=CALCULATE(COUNT('Table'[Column]),FILTER(ALLSELECTED('Table'),[Column]>TODAY()))
RETURN IF(MAX('Calendar'[Date])<=TODAY(),_Actual,_Forecast)

(3)Then the result is as follows.

If the above one can't help you get the desired result, please provide your expected result with backend logic and special examples. Thank you.

Best Regards,

Neeko Tang

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