Forum Discussion

carlenb's avatar
carlenb
Advocate II
2 years ago
Solved

Cumulative progress chart [Model attached]

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 i...
actual
DAX
forecast
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

     

carlenb's avatar
carlenb
Advocate II
2 years ago

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 doneReschedule 1Reschedule 2StatusContract done
2023-04-25  Done2023-04-25
2023-05-15  Done2023-06-15
2024-01-032024-03-012024-06-05Delayed 
2024-02-032024-07-01 Delayed 
2024-03-01  Done2024-03-01
2024-04-03  Progressing 
2024-06-03  Delayed 
2024-08-01  Progressing 

 

Expected output based on data set: 

 

 ActualPlannedForecast
apr-2311 
maj-2312 
jun-2322 
aug-2322 
sep-2322 
okt-2322 
nov-2322 
dec-2322 
jan-2423 
feb-2424 
mar-24353
apr-24 64
maj-24 64
jun-24 75
jul-24 75
aug-24 86

 

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    carlenb
    Advocate II
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.