CODEWITHSUNDEEP
powerbidax
thothal
thothal

Reputation: 20369

Measure which is set to 0 for all but the first date

I want to define a measure which returns the sum of a certain column only for the first date in the range, otherwise it should return the value 0. Idea is that I want to use this measure as a baseline in a waterfall chart which I will add to the delta values.

Example Data

Date Backlog
1.1.2021 10
1.1.2021 12
2.1.2021 11
2.1.2021 13
3.1.2021 14
3.1.2021 15

Expected Result

Date Backlog
1.1.2021 22
2.1.2021 0
3.1.2021 0

Upvotes: 0

Views: 132

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

You can define measure like this:

Measure = 
var minDate = MINX(ALLSELECTED('Table'), 'Table'[Date])
return IF(MAX('Table'[Date]) = minDate, SUM('Table'[Backlog]), 0)

Where you will get the minimum date in the current filter context and then sum up the values up to this date, or 0 for the dates after.

enter image description here

If you want to use the minimum date available, regardless of the current filter, replace ALLSELECTED with ALL in the code above.

Upvotes: 2

Related Questions