Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

De-cumulative values by date

Hello,

 

I have a data set that contains a cumulative column (it is connected to the BI service so I am trying to avoid using a calculated column, ideally I need a DAX measure).

 

I need to produce a line graph that always starts at zero and goes up using this cumulative column.

So essentially minus the earliest date selected from the whole column (when sliced) to start the line at zero and climb upwards.

 

Ive had an attempt at getting the EARLIEST date and minus it from the data but cant seem to get it to work...

 

Any ideas would be appreciated.

 

Thanks,

 

Example:

 

01/01/201710
01/02/201715
01/03/201720
01/04/201725
01/05/201830
01/06/201735

 

So the when sliced by 01/03/2017 onwards the line would start at 0 and then show 5, 10, 15.

  • HI Anonymous

     

    Try this MEASURE

     

    Measure =
    VAR EarliestDate =
        CALCULATE ( MIN ( TableName[Date] ), ALLSELECTED () )
    VAR StartValue =
        CALCULATE (
            SUM ( TableName[Cumulative Values] ),
            TableName[Date] = EarliestDate
        )
    RETURN
        SUM ( TableName[Cumulative Values] ) - StartValue

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion

    HI Anonymous

     

    Try this MEASURE

     

    Measure =
    VAR EarliestDate =
        CALCULATE ( MIN ( TableName[Date] ), ALLSELECTED () )
    VAR StartValue =
        CALCULATE (
            SUM ( TableName[Cumulative Values] ),
            TableName[Date] = EarliestDate
        )
    RETURN
        SUM ( TableName[Cumulative Values] ) - StartValue