Forum Discussion

Reddyp's avatar
Reddyp
Icon for Helper I rankHelper I
4 years ago
Solved

Sales for particular year_DAX query help

1. I want help in writing DAX for calculating "sales" for particular year (example:sum of sales where year  = ‘1900-01-01 00:00:00')

2. for the above DAX formula I need to add "location" of store, ("sales" for particular year where location is "Auckland")

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi Reddyp ,

     

    You could try below code:-

    CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( Table1, Table1[Date] = DATE ( 1900, 01, 01 ) ),
    FILTER ( Table2, Table2[Location] = "Auckland" )
)

     

    BR,

    Samarth

3 Replies

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

    Reddyp ,

    calculate( sum(Table[Amount]), filter(Table, Table[Date] = date(1900,01,01)  && Table[Location] = "Auckland") )

    • Reddyp's avatar
      Reddyp
      Icon for Helper I rankHelper I
      4 years ago

      amitchandak this can't be worked when "Location" details are on different table (Table 2) right ?

      calculate( sum(Table1[Amount]), filter(Table1, Table[Date] = date(1900,01,01)  && Table1[Location] = "Auckland") )

      help me out in applying filter on columns from different tables 

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Reddyp ,

     

    You could try below code:-

    CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( Table1, Table1[Date] = DATE ( 1900, 01, 01 ) ),
    FILTER ( Table2, Table2[Location] = "Auckland" )
)

     

    BR,

    Samarth