[MEASURE] Cumulative / Target Indicator

[ferbypbi]

guys, I am working on this example below:

Target:	10
	Sales	Cumulative
01/01/2021	1	1
02/01/2021	3	4
03/01/2021	4	8
04/01/2021	5	13
Days untill reach target:	4

I created the cumulative sum:
Calculate( 
   Sum( table[column]),
       Filter(
           All(DateDim),
              DateDim[Date] <= Max(DateDim[Date])

its working!

But when I trying to create the indicator to see how many days until reach the target I am totally lost.

Target is sum of a table´s column.

Does anyone know how to start? I am stuck on this..

[v-yiruan-msft · Accepted solution]

Hi @ferbypbi ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to get the culmulative value

Cumulative = 
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@cumulative",
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
            )
    )
RETURN
    SUMX ( _tab, [@cumulative] )

2. Create a measure as below to get the number of days which achive the target

The number of days which reach the target = 
VAR _target = 10
VAR _mindate =
    MINX ( ALL ( 'Table' ), 'Table'[Date] )
VAR _tdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Cumulative] > _target )
    )
RETURN
    IF (
        _mindate = _tdate,
        1,
        IF ( _mindate < _tdate, DATEDIFF ( _mindate, _tdate, DAY ) + 1 )
    )

Best Regards

[VahidDM]

Hi @ferbypbi 

How did you calculate Days untill reach target: 4??  Can you add more details re the calculation?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ferbypbi]

hello VahidDM,

Basically if the cumulative sum passes the target and from the beginning how many days have passed