How to calculate the sum before the selected date?

[Shineccx007]

Hello,

I wanted to calculate the accumulated quantity sum from min start date untill the one day before date I selected. Is this DAX correct?

I created a dim_date table calendar and this table is linked to item ledger entries table, one to many relationship,
Can anyone help?

Thanks a bunch!

[talespin]

Please try and let me know.

//This will execute in different filter context(each date in visual).

Sum Quantity Before Selected Date =

//This will be minimum of date table

VAR minDate = CALCULATE(MIN('dim_date'[Date]), ALL('dim_date'[Date]))

//This will be one day less than the date filter from visual.

VAR maxDate = MAX('dim_date'[Date]) - 1

return CALCULATE(SUM(ItemLedgerEntries[Quantity]), 'dim_date'[Date] >= minDate, 'dim_date'[Date] <= maxDate)

[some_bih]

Hi @Shineccx007 

possible alternative as following:

part of measure starting with CALCULATE, part DATESINPERIOD replace with

FILTER (
'dim_date',
'dim_date'[Date] >= Start_day &&
'dim_date'[Date] <= Last_day
)

[Shineccx007]

Hi Bih,

Seems this solution not working on my end.

I think power bi showed wrong on my DAX showing the date before I selected. selected value of the date -1 is not working in my measure dax.