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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MSFTPBI
Regular Visitor

Sum of Revenue upto Selected Date

Hello,

I have a table Table1, with RevenueDate, and Revenue
I have a slicer in report on RevenueDate. User can select range of dates, but I want to ignore Initial selected date and just provide Revenue upto end date.

Example: First date in table is 01/01/23 and Last Date is 3/20/23
If User Selects

02/02/23  and 3/15/23

I need to show Sum of Revenue Falls between 01/01/23 (ignoring 02/02/23) to 3/15/23

 

Apprecite your help.

1 ACCEPTED SOLUTION

@MSFTPBI Some syntax errors. I think I got all of them, extra ]'s and a missing )

REVDate =
var vMin = MINX(ALL('TABLE1'), 'TABLE1'[Revenue Date])
var vMAX = MAX('Table1'[Revenue Date])
var vTab = FILTER(All('Table1'),[Revenue Date] >= vMin &&  [Revenue Date]) <=  vMAX)
var vResult= SUMX(vTab, 'Table1'[Revenue])
RETURN
vResult

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@MSFTPBI Try:

Measure =
  VAR __Min = MINX(ALL('Table'), [Date])
  VAR __Max = MAX('Table'[Date])
  VAR __Table = FILTER(ALL('Table'), [Date] >= __Min && [Date] <= __Max)
  VAR __Result = SUMX(__Table, [Revenue])
RETURN
  __Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  getting error"The Expreession refers to multiple columns. Multiple columns cannot be convertes to a scalar value.
Here is my DAX

REVDate =
var vMin = MINX(ALL('TABLE1'), 'TABLE1'[Revenue Date])
var vMAX = MAX('Table1'[Revenue Date])
var vTab = Filter(All('Table1'),[Revenue Date]] >= vMin &&  [Revenue Date]]) <=  vMAX
var vResult= SUMX(vTab, 'Table1'[Revenue])
RETURN
vResult

@MSFTPBI Some syntax errors. I think I got all of them, extra ]'s and a missing )

REVDate =
var vMin = MINX(ALL('TABLE1'), 'TABLE1'[Revenue Date])
var vMAX = MAX('Table1'[Revenue Date])
var vTab = FILTER(All('Table1'),[Revenue Date] >= vMin &&  [Revenue Date]) <=  vMAX)
var vResult= SUMX(vTab, 'Table1'[Revenue])
RETURN
vResult

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Decklersomehow still not working

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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