Need help in Dax (Date based calculation)

[Anonymous]

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!

[amitchandak · Accepted solution]

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.

[Anonymous]

Thank you very very much @amitchandak for help.

It solved my problem 🙂

[Greg_Deckler]

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]

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!