Forum Discussion
Correct date with Dax multiple records
Hello,
Is there a way to build a Dax formula to give me the # of Days based on the Target Start Date and Target End Date (per below table)? So, I put a graph and the days based on a dates table the formula will grab the correct # of days based on the Target Start and End Date.
For example if I had a table for all of 2022: Jane Smith would return 4 (# of Days) for Jan 1 - 24 and 3 for the rest of the year. In another example for Christiane Good it would return 3 for Jan 1 - Mar 13, 2.5 Mar 14 - Mar 15 and 3 Mar 16 for the rest of the year.
6 Replies
vapid128Solution Specialist
Jane Smith would return 4 (# of Days) for Jan 1 - 24
what is the relationship between 4 and jan1 jan24?
vapid128 4 would be the # of days according to the table if I wanted to chart it on a graph for 2022 that would be the result I want for Jan 1 - 24 as the Target on the 2nd line says its effective from Mar 15 2018 - Jan 24 2022.
Hi Anonymous ,
I'm sorry, but despite your explanation, I still don't understand what you mean
How do you get 4 for the number of days? From what calculation? Why is it 4, is it 2022-2018=4?How do you explain the number of days for Danielle Food? I don't understand on what basis you calculated it?
Why does the number of days for Deanna Run become 5?
That's weird!Could you please provide more details?
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous There is no calculations. The number is in the table above its the 4th column highlighted in yellow called # of Days. This number changes based on the Target Start and End Date the 6th and 7th column in the table. For Danielle Food 1st Target Start Date is on the 6th row and Target Start Date is Nov 16, 2021 to Target End Date Mar 13, 2022 and is 3 which is what I want to return on a graph for 2022 from Jan 1, 2022 - Mar 13, 2022.
Then the 5th row the Target Start Date is Mar 14, 2022 to Target End Date Mar 15, 2022 and is 2.5 which is what I want to return on a graph for 2022 Mar 14, 2022 - Mar 15, 2022. Then on the 4th row is Target Start Date of Mar 16, 2022 and No Target End Date but this target is active per the Target Status (8th column) meaning on a graph of 2022 I want to use now its 3 so from Mar 16, 2022 to current date I want 3.
I want if possible a formula that would be able to grab the correct # of Days (column 4) using the table based on the Target End Date (Column 6) and Target End Date (Column 7).
Hi Anonymous ,
Could you please provide a simple sample (without privacy information) and desired output (It is best to use images to represent your example data and the desired output separately).
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.