Forum Discussion

jay_patel's avatar
jay_patel
Icon for Helper IV rankHelper IV
4 years ago
Solved

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.

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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]))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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).)