DAX code result changes based on column position. How to fix this?

[Lori001]

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.

[v-binbinyu-msft]

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

[Lori001 · Accepted 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.

PREVIEW