Relationship and Calculation between Tables with Time Series Data

[kolturra]

Hi,

I am trying to understand whether DAX or Relationship works to get date & time results, below the two tables for your reference. I have attached excel file in below link

https://github.com/kolturra/Python/blob/master/DAX%20Date%20and%20Time.xlsx

Please click on download to get file.

Table 1 - Cost details – Whenever changes happen in cost those transactions will be saved in table 1 with start and end date & time

Table 2 - Break Table - Whenever break is taken by ID that will be tracked with start datetime and End datetime 

Can someone help me in creating DAX to get cost details with combination of start and end datetime from both the tables.

In table 1 & 2 mainly look at the 16 May 2019 date, cost got changed twice and details has updated accordingly in table 2 as per time

I have created results in table 2 in Results section. Please help me in this

Thanks,

K Raghavender Rao

[v-xicai]

Hi  @kolturra ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @kolturra ,

You can create columns in 'Break Table' like DAX below.

Cost in Rupees =

var d1 = maxx(TOPN(1,FILTER('Cost details','Cost details'[Start D & T]<='Break Table'[Start D & T]),'Cost details'[Start D & T],DESC),'Cost details'[No Job Cost in Rupees])

var d2 = maxx(TOPN(1,FILTER('Cost details','Cost details'[Start D & T]<='Break Table'[Start D & T]),'Cost details'[Start D & T],DESC),'Cost details'[Cost in Rupees])

return

IF('Break Table'[Reason]="No job", IF(d1=BLANK(),d2,d1),d2)

Loss in Rupees = 'Break Table'[Break Time in Minutes]*'Break Table'[Cost in Rupees]

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.