topic Re: Cumulative progress chart [Model attached] in DAX Commands and Tips

Cumulative progress chart [Model attached]

carlenb — Wed, 27 Mar 2024 12:46:08 GMT

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

Re: Cumulative progress chart [Model attached]

Anonymous — Thu, 28 Mar 2024 07:46:15 GMT

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.

Re: Cumulative progress chart [Model attached]

carlenb — Sat, 30 Mar 2024 16:56:18 GMT

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!

Re: Cumulative progress chart [Model attached]

Anonymous — Mon, 01 Apr 2024 02:23:11 GMT

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.

Re: Cumulative progress chart [Model attached]

carlenb — Mon, 01 Apr 2024 16:10:59 GMT

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!

Re: Cumulative progress chart [Model attached]

Anonymous — Tue, 02 Apr 2024 01:29:30 GMT

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.