Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help in Dax (Date based calculation)

Hello Dax Experts,

 

I need help in DAX.

 

I have 2 tables in my data:

 

Table1
Parameter1Parameter2Date1Amount
AX01-01-201910
BX02-01-201920
CX03-01-201930
DX04-01-201915
EX05-01-20195
FY01-01-201921
GY02-01-201930
HY03-01-201911
IY04-01-201923
JY05-01-201954
KZ10-11-201823
LZ11-11-201865
MZ12-11-201812
NZ13-11-201876
OZ14-11-201876

 

Table2
Parameter1Date2Units
A01-11-20181
A01-12-20182
A03-01-20193
A05-01-20194
A10-01-20195
B01-01-201910
B02-01-201911
B03-01-201912
B04-01-201913
C01-01-201914
C02-01-201915
C03-01-201916
C04-01-201917
C01-01-201918
D02-01-201919

 

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
Parameter2AmountUnits
X506
Y8833

 

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!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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
Not applicable

Thank you very very much @amitchandak for help.

It solved my problem 🙂

 

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.