Forum Discussion
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 | 30 |
| D | X | 04-01-2019 | 15 |
| E | X | 05-01-2019 | 5 |
| F | Y | 01-01-2019 | 21 |
| G | Y | 02-01-2019 | 30 |
| H | Y | 03-01-2019 | 11 |
| I | Y | 04-01-2019 | 23 |
| J | Y | 05-01-2019 | 54 |
| K | Z | 10-11-2018 | 23 |
| L | Z | 11-11-2018 | 65 |
| M | Z | 12-11-2018 | 12 |
| N | Z | 13-11-2018 | 76 |
| O | Z | 14-11-2018 | 76 |
| Table2 | ||
| Parameter1 | Date2 | Units |
| A | 01-11-2018 | 1 |
| A | 01-12-2018 | 2 |
| A | 03-01-2019 | 3 |
| A | 05-01-2019 | 4 |
| A | 10-01-2019 | 5 |
| B | 01-01-2019 | 10 |
| B | 02-01-2019 | 11 |
| B | 03-01-2019 | 12 |
| B | 04-01-2019 | 13 |
| C | 01-01-2019 | 14 |
| C | 02-01-2019 | 15 |
| C | 03-01-2019 | 16 |
| C | 04-01-2019 | 17 |
| C | 01-01-2019 | 18 |
| D | 02-01-2019 | 19 |
Table1 is having relationship with table2 as one to many (one side at table 1) on Parameter 1.
I need to filter table1 as on or after Date1(from table 1) and take corresponding sum(units) for respective parameter1 from table2. But from table 2 the Date2 should be on or before.
For ex: If we select Date1 slicer as:
| Date1Slicer(on or after) |
| 03-01-2019 |
The output should be coming as :
| Output | ||
| Parameter2 | Amount | Units |
| X | 50 | 6 |
| Y | 88 | 33 |
Please note that amout is getting summed up as on or after 03-01-2019 but units are getting summed up as on or before 03-01-2019.
Request you to please help here.
Thanks!
Create a common dimestion DATE
Create a common using Parameter1 Parameter2 from table 1like
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.
4 Replies
- amitchandak
Super User
Create a common dimestion DATE
Create a common using Parameter1 Parameter2 from table 1like
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
Community Champion
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.
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!
