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
Jack_Reacher
Helper I
Helper I

Dates from single Date Column do not produce expected result

Hello, 

I'm seeking your expertise to help me improve two DAX calculations that I'm currently using to calculate the receiving and sending (handling) dates for a ticket table. The data I'm dealing with is related to a list of tickets that are received and then treated by technicians on different dates, unfortunately, I'm only provided with one date column which aggregates all actions performed on a specific ticket (which is DateTime), and a ticket may appear repetitively but with different receiving and sending dates (which should be taken into account of course). 

Here's a snapshot from the raw data table:

Feel free to download these test data tables from here: "https://smallpdf.com/file#s=c745e973-29a0-4dbd-85ab-2472d7379858"


Below are the DAX calculations I'm using to produce the results (which are now inaccurate)

ReceiverDate = 
CALCULATE(
MIN(WEEKLY_IDs[DateTime]),
FILTER(
ALL('WEEKLY_IDs'),
'WEEKLY_IDs'[SenderID] = EARLIER('WEEKLY_IDs'[ReceiverID]) &&
'WEEKLY_IDs'[ReceiverID] = EARLIER('WEEKLY_IDs'[SenderID]) &&
'WEEKLY_IDs'[Ticket_ID] = EARLIER('WEEKLY_IDs'[Ticket_ID])
)
)

SenderDate =
CALCULATE(
MAX([DateTime]),
FILTER(
ALL('WEEKLY_IDs'),
'WEEKLY_IDs'[SenderID] = EARLIER('WEEKLY_IDs'[SenderID]) &&
'WEEKLY_IDs'[ReceiverID] = EARLIER('WEEKLY_IDs'[ReceiverID]) &&
'WEEKLY_IDs'[Ticket_ID] = EARLIER('WEEKLY_IDs'[Ticket_ID])
)
)
 

Jack_Reacher_1-1685573193256.png

 

These are the end results I'm seeking to accomplish with these two DAX calculations:

Jack_Reacher_2-1685573193252.png

 

Any idea how to fine-tune these calcs please?

 

 

1 ACCEPTED SOLUTION

Hi , @Jack_Reacher 

Thanks for your qucik response and your sample data!

According to your description and your expected result data , I am not sure that I fully understand your calculation logic. According to the comparison, I directly sort your dates and get the start and end time through chronological order, but I have not found the calculation rules for your calculation logic of other columns. I am in To implement in Power Query, you can try the following methods to see if they are applicable to your situation, you can put this M code in your blank query , and the sample data table in my side is called 'Table':

let
    Source = Table.Group(Table,"DOSSIER_Nb",{"test",(x)=>Table.Distinct(Table.Sort(x,"DateTimez") )      }),
    #"Added Custom" = Table.AddColumn(Source, "Custom", (x)=>if Number.Mod(Table.RowCount(x[test]),2) =0 then {1..Table.RowCount(x[test])/2} else {1..(Table.RowCount(x[test])+1)/2}    ),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "ReceiverDate", (x)=>    
if Number.Mod(Table.RowCount(x[test]),2)<>0 and (Table.RowCount(x[test])+1)/2 =x[Custom] then  x[test]{Table.RowCount(x[test])-2}[DateTimez] 
else x[test]{2*(x[Custom]-1)}[DateTimez]  
   ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "SenderDate",  (x)=>    
if Number.Mod(Table.RowCount(x[test]),2)<>0 and (Table.RowCount(x[test])+1)/2 =x[Custom] then  x[test]{Table.RowCount(x[test])-1}[DateTimez] 
else x[test]{2*(x[Custom]-1)+1}[DateTimez]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"test", "Custom"})
in
    #"Removed Columns"

You can try to replace the table name in your side:

vyueyunzhmsft_0-1686102328567.png

The result is as follows:

vyueyunzhmsft_1-1686102344586.png

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

View solution in original post

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

Hi,  @Jack_Reacher 

According to your dax code, you are using the EARLIER function, which means the current row, and you use 'WEEKLY_IDs'[SenderID] = EARLIER('WEEKLY_IDs'[ReceiverID]), which means the current row [SenderID]=[ ReceiverID] is True.

If you are using measure, generally we will not use EARLIER() as the judgment logic, which is generally used in calculated columns. Do you want to use calculated columns or measures?

In the meantime,I tried to sign in with gmail account but still don't have access to download this file link you shared. If convenient, would you like to kindly check the access situation of this link and open the public access in order for our downloading?

 vyueyunzhmsft_2-1685677310910.png

 

 

And according to your last screenshot, I still don't quite understand how you got the final result. For your question, can you provide me with some partitial test data (including the case of repeated values you mentioned), and Provide me with the correct end result you want to output in the form of a table, it will make the problem more clear and understandable.
Thanks for your DAX sharing, only DAX without test data, it is difficult to provide you with corresponding support directly.

Finally, I am very sorry. I have 4 days of vacation in the future. I am looking forward to your reply. I will contact you as soon as I come back~

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

 

 

Hello @v-yueyunzh-msft , for the DAX Earlier function I used here, they only apply to "New Columns" and not measures so yes they're both column measures since I couldn't find a good DAX measure to implement. 
Sorry about the broken link, I tried to upload documents along with my post in the community but it doesn't give the option to do that. This link should work fine now: https://docs.google.com/spreadsheets/d/19DnS6zFL-XgLvC9GNft-zLcqHm_Mif1g/edit?usp=sharing&ouid=10719...
It contains two tabs, one for the raw data from a much bigger dataset, and the other tab includes the table with the two columns I want to produce thanks to the calculated columns I posted, these columns are named ReceiverDate, and SenderDate. 
The logic I used in the calculated columns (and I might have missed something) is that for each receiver and sender id, I want to output the DateTime aligned with it, then later I will create another calculated column to produce the duration each ticket took to get treated. 
I'm looking forward to your response, and thank you in advance. 
 

Hi , @Jack_Reacher 

Thanks for your qucik response and your sample data!

According to your description and your expected result data , I am not sure that I fully understand your calculation logic. According to the comparison, I directly sort your dates and get the start and end time through chronological order, but I have not found the calculation rules for your calculation logic of other columns. I am in To implement in Power Query, you can try the following methods to see if they are applicable to your situation, you can put this M code in your blank query , and the sample data table in my side is called 'Table':

let
    Source = Table.Group(Table,"DOSSIER_Nb",{"test",(x)=>Table.Distinct(Table.Sort(x,"DateTimez") )      }),
    #"Added Custom" = Table.AddColumn(Source, "Custom", (x)=>if Number.Mod(Table.RowCount(x[test]),2) =0 then {1..Table.RowCount(x[test])/2} else {1..(Table.RowCount(x[test])+1)/2}    ),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "ReceiverDate", (x)=>    
if Number.Mod(Table.RowCount(x[test]),2)<>0 and (Table.RowCount(x[test])+1)/2 =x[Custom] then  x[test]{Table.RowCount(x[test])-2}[DateTimez] 
else x[test]{2*(x[Custom]-1)}[DateTimez]  
   ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "SenderDate",  (x)=>    
if Number.Mod(Table.RowCount(x[test]),2)<>0 and (Table.RowCount(x[test])+1)/2 =x[Custom] then  x[test]{Table.RowCount(x[test])-1}[DateTimez] 
else x[test]{2*(x[Custom]-1)+1}[DateTimez]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"test", "Custom"})
in
    #"Removed Columns"

You can try to replace the table name in your side:

vyueyunzhmsft_0-1686102328567.png

The result is as follows:

vyueyunzhmsft_1-1686102344586.png

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

Thank you @v-yueyunzh-msft , I managed to merge the query with the rest of the dataset, and the dates look much better now

@v-yueyunzh-msft , welcome back from your vacation, hope you had restful days 😊 , sorry for the late reply as I was swamped with work, I have to admit the logic used in the calculated columns I came with are limited since I'm fairly new to the Power BI software. Apart from that, the calculations you provided here seem to be very logical and the outputs I'm seeing look much more accurate. 

I will try these calculations, and revert back to you. 
Thank you so much for your time and help on this issue!!

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.