Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi community,
I have a Dax code challenge and don't know how to fix this.
I have a table where I want to calculate the date difference between the columns StartDate and EndDate.
The result should be the difference in days.
If there is no EndDate, the result should be the date difference between StartDate and Date today.
The dax formula that I used looks like this:
DurationDays = DATEDIFF('Car'[StartDate],IF(ISBLANK(RELATED('Paper'[EndDate)),TODAY(),RELATED('Paper'[EndDate])), DAY) |
This should be the correct formula, but depending on the position where I add the produced dax column DurationDays, I get different results:
In this example, I get the correct table rows, but I'm missing the diference in days when EndDate is not filled in:
IDStartDateAmountTotal amountEndDateDurationDays
1 | 1-1-2023 | ||||
2 | 1-1-2023 | ||||
3 | 1-1-2023 | ||||
4 | 1-1-2023 | 700 | 1400 | 15-2-2023 | 45 |
5 | 1-1-2023 | 700 | 1400 | 22-2-2023 | 52 |
6 | 1-1-2023 | ||||
7 | 1-1-2023 | ||||
8 | 1-1-2023 | ||||
9 | 1-1-2023 | 300 | 700 | 15-2-2023 | 45 |
10 | 1-1-2023 | 300 | 700 | 16-2-2023 | 45 |
11 | 1-1-2023 | ||||
12 | 1-1-2023 | ||||
13 | 1-1-2023 | 100 | 300 | 15-2-2023 | 45 |
In example two, I change the position of DurationDays to the end of the table visualization and then I see the difference in days of the empty EndDate according the dax code.
The problem now is that I see two rows:
- One row which takes the difference between StartDate and filled in EndDate. I always want to see this when EndDate has a value.
- One row which takes the difference between StartDate and empty EndDate. In only want to see this when EndDate has no value.
IDStartDateAmountTotal amountDurationDaysEndDate
1 | 1-1-2023 | 85 | |||
2 | 1-1-2023 | 85 | |||
3 | 1-1-2023 | 85 | |||
4 | 1-1-2023 | 700 | 1400 | 45 | 15-2-2023 |
4 | 1-1-2023 | 85 | |||
5 | 1-1-2023 | 700 | 1400 | 52 | 22-2-2023 |
5 | 1-1-2023 | 85 | |||
6 | 1-1-2023 | 85 | |||
7 | 1-1-2023 | 85 | |||
8 | 1-1-2023 | 85 | |||
9 | 1-1-2023 | 300 | 700 | 45 | 15-2-2023 |
9 | 1-1-2023 | 85 | |||
10 | 1-1-2023 | 300 | 700 | 45 | 16-2-2023 |
10 | 1-1-2023 | 85 | |||
11 | 1-1-2023 | 85 | |||
12 | 1-1-2023 | 85 | |||
13 | 1-1-2023 | 100 | 300 | 45 | 15-2-2023 |
13 | 1-1-2023 | 85 |
What I would like to see is below example:
One table with each ID occuring only once.
IDStartDateAmountTotal amountEndDateDurationDays
1 | 1-1-2023 | 85 | |||
2 | 1-1-2023 | 85 | |||
3 | 1-1-2023 | 85 | |||
4 | 1-1-2023 | 700 | 1400 | 15-2-2023 | 45 |
5 | 1-1-2023 | 700 | 1400 | 22-2-2023 | 52 |
6 | 1-1-2023 | 85 | |||
7 | 1-1-2023 | 85 | |||
8 | 1-1-2023 | 85 | |||
9 | 1-1-2023 | 300 | 700 | 15-2-2023 | 45 |
10 | 1-1-2023 | 300 | 700 | 16-2-2023 | 45 |
11 | 1-1-2023 | 85 | |||
12 | 1-1-2023 | 85 | |||
13 | 1-1-2023 | 100 | 300 | 15-2-2023 | 45 |
Unfortunately I don't seem to get this work.
Thank you for your help.
Solved! Go to Solution.
Hi binbinyu,
Apoligies for my late reply. Didn't see the notification and I also thought it was marked as spam.
How I solved the matter was by shifting the columns accordingly. Basically I changed the orders and that allowed me to see what I wanted to see.
My table had more columns so cannot show it here, but I guess its clear how I solved the matter.
Thank you for your response anyway.
Hi @Lori001 ,
This problem may be caused by the data model.
Could you please provide desensitized example data? It is very helpful for me to test.
Thanks for your efforts & time in advance.
Best regards,
Community Support Team_Binbin Yu
Hi binbinyu,
Apoligies for my late reply. Didn't see the notification and I also thought it was marked as spam.
How I solved the matter was by shifting the columns accordingly. Basically I changed the orders and that allowed me to see what I wanted to see.
My table had more columns so cannot show it here, but I guess its clear how I solved the matter.
Thank you for your response anyway.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |