Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help in Dax (Date based calculation)

Hello Dax Experts,   I need help in DAX.   I have 2 tables in my data:   Table1 Parameter1 Parameter2 Date1 Amount A X 01-01-2019 10 B X 02-01-2019 20 C X 03-01-2019 ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Create a common dimestion DATE
    Create a common using Parameter1 Parameter2 from table 1

    like
    Table 3 = distinct(summarize(Table1,table1[Parameter1],table1[Parameter2])
    Join to both tables using parameter 1


    Value =
    var _max = minx(Date,Date[Date])

    return
    calculate(count(table1[Amount],filter(all(Date),Date[Date]>=_max))


    Unit =
    var _max = minx(Date,Date[Date])

    return
    calculate(count(table2[Unit],filter(all(Date),Date[Date]<=_max))

     

    Appreciate your Kudos.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Can you explain the logic of where the unit values 6 and 33 are coming from? I am not clear on this. Just in plain language.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello Greg,

 

Sure 🙂

 

Units is coming from Table2. Sum of units where Date2 is less then or equal to 03-01-2019.

Amount is coming from Table1. Sum of Amount where Date1 is more then or equal to 03-01-2019.

 

We have Date1 as a slicer as on or before in the report.

 

I hope I am clear enough now 🙂

 

Thanks!