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

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.

 

Kryssy_0-1655489752690.png

 

6 REPLIES 6
v-rongtiep-msft
Community Support
Community Support

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

@v-rongtiep-msft 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).

Anonymous
Not applicable

@v-rongtiep-msft  I needed it to be daily so to get it to fit I broke it into quarters. Here is the desired result I am looking for. I color coded the changes which are according to the first table above. 

Happy to provide any further information that is needed just let me know.

 

Kryssy_0-1655899696545.png

 

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.

vapid128
Solution Specialist
Solution Specialist

Jane Smith would return 4 (# of Days) for Jan 1 - 24

 

what is the relationship between 4 and jan1 jan24?

Anonymous
Not applicable

@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.

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.

Top Solution Authors