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
jay_patel
Helper IV
Helper IV

Calculate sales based on selected date

Hi all,

I am stucked while calculating sales with different conditions. Let me explain in detail

I have a calendar table and a Sales table, and looking for a dax where if i am selecting any date using calendar date which is not equal to current data then the dax should calculate sales where the selected date should  less than or equal to posting date and selected date is greater than Clearing date.  and Condition type = BSAD (Note:- Posting date & Clearing date & Condition Type are 3 different columns in Sales Table).

Thanks in advance.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jay_patel , Try a measure like

 


Measure =
var _max = maxx(allselected('Date1'),'Date1'[Date])
var _m = Calculate(sum(Table[Value]), filter(Table, Table[Posting Date] >=_max && Table[Cleaning Date] <=_max && [Condition type] = "BSAD"))
return
if(isblank(sum(Table[Value])), _m, sum(Table[Value]))

 

 

or

 

 

 

If date tble is joined
Measure =
var _max = maxx(allselected('Date1'),'Date1'[Date])
var _m = Calculate(sum(Table[Value]), filter(all(Table), Table[Posting Date] >=_max && Table[Cleaning Date] <=_max && [Condition type] = "BSAD"))
return
if(isblank(sum(Table[Value])), _m, sum(Table[Value]))

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi jay,

 now i am facing same issue with vendors data, 

please consider the below text.

 (Hi all,

I am stucked while calculating sales with different conditions. Let me explain in detail

I have a calendar table and a Sales table, and looking for a dax where if i am selecting any date using calendar date which is not equal to current data then the dax should calculate sales where the selected date should  less than or equal to posting date and selected date is greater than Clearing date.  and Condition type = BSAD (Note:- Posting date & Clearing date & Condition Type are 3 different columns in Sales Table).)

amitchandak
Super User
Super User

@jay_patel , Try a measure like

 


Measure =
var _max = maxx(allselected('Date1'),'Date1'[Date])
var _m = Calculate(sum(Table[Value]), filter(Table, Table[Posting Date] >=_max && Table[Cleaning Date] <=_max && [Condition type] = "BSAD"))
return
if(isblank(sum(Table[Value])), _m, sum(Table[Value]))

 

 

or

 

 

 

If date tble is joined
Measure =
var _max = maxx(allselected('Date1'),'Date1'[Date])
var _m = Calculate(sum(Table[Value]), filter(all(Table), Table[Posting Date] >=_max && Table[Cleaning Date] <=_max && [Condition type] = "BSAD"))
return
if(isblank(sum(Table[Value])), _m, sum(Table[Value]))

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.