cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Need help in Dax (Date based calculation)

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Need help in Dax (Date based calculation)

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Need help in Dax (Date based calculation)

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper I
Helper I

Re: Need help in Dax (Date based calculation)

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!

 

Highlighted
Helper I
Helper I

Re: Need help in Dax (Date based calculation)

Thank you very very much @amitchandak for help.

It solved my problem 🙂

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors