Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Shineccx007
Helper I
Helper I

How to calculate the sum before the selected date?

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!

Shineccx007_0-1705861579638.png

 

3 REPLIES 3
talespin
Solution Sage
Solution Sage

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

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
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.

 

Shineccx007_0-1705896148738.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.