cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chris_k Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: De-cumulative values by date

HI @chris_k

 

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 3
Super User
Super User

Re: De-cumulative values by date

HI @chris_k

 

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
Super User
Super User

Re: De-cumulative values by date

chris_k Regular Visitor
Regular Visitor

Re: De-cumulative values by date

Worked perfectly. Thank you!