cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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
Regards,
Zubair


View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

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
Regards,
Zubair


View solution in original post

Highlighted
Super User I
Super User I

Re: De-cumulative values by date

@chris_k

 

1094.png

Regards,
Zubair


Highlighted
Helper I
Helper I

Re: De-cumulative values by date

Worked perfectly. Thank you!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors