calculated column to return one value in the report

[KV2022]

Hi, I am trying to calculate sum(days) in one table 1

ID stg1    days

ID stg2    days

I need to calculate sum(days) for the ID

for a calc column in a fact table which has

ID stg1 iter1 

ID stg1 iter2

ID stg2 iter1

ID stg2 iter2

The calculated columns has to give one value for the ID  in the report

[v-yadongf-msft]

Hi @KV2022 .

Remove +1 and try again:

CALCULATE(sumx(fact_doc_stg_evnt, DATEDIFF(mindate,maxdate,DAY))

Best regards,

Yadong Fang

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

[KV2022]

If i remove +1 , I am getting a day less than the required no of days.  

[v-yadongf-msft]

Hi @KV2022 ,

This is my test table:

Please try following DAX:

Measure = DATEDIFF(MIN('Table'[Start date]),MAX('Table'[End date]),DAY)

Is this the result you want?

If I misunderstand your demands, please feel free to contact us in time.

Best regards,

Yadong Fang

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

[KV2022]

I dont want this as a measure. I want the difference of dates in a calculated column

[v-yadongf-msft]

Hi @KV2022 ,

Ok, please try following DAX:

column = 
var min_start_date = MINX(FILTER('Table','Table'[ID] = EARLIER('Table'[ID])),'Table'[Start date])
var max_end_date = MAXX(FILTER('Table', 'Table'[ID] = EARLIER('Table'[ID])),'Table'[End date])
RETURN 
DATEDIFF(min_start_date,max_end_date,DAY)

I think this is the result you want:

Best regards,

Yadong Fang

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

[KV2022]

thanks. But your calculation is giving the wrong results. 

I tried this ..This works. But it gives one day less 

Total Duration by fact =

 var mindate = CALCULATE(minx(fact_doc_stg_evnt,fact_doc_stg_evnt[actl_strt_dttm]))

var maxdate = CALCULATE(maxx(fact_doc_stg_evnt,fact_doc_stg_evnt[actl_end_dttm]))

return

CALCULATE(sumx(fact_doc_stg_evnt, DATEDIFF(mindate,maxdate,DAY)),

REMOVEFILTERS(fact_doc_stg_evnt[days_in_support],fact_doc_stg_evnt[times_in_hold],fact_doc_stg_evnt[times_in_support],fact_doc_stg_evnt[days_in_hold]))

So I tried this 

Total Duration by fact =

 var mindate = CALCULATE(minx(fact_doc_stg_evnt,fact_doc_stg_evnt[actl_strt_dttm]))

var maxdate = CALCULATE(maxx(fact_doc_stg_evnt,fact_doc_stg_evnt[actl_end_dttm]))

return

CALCULATE(sumx(fact_doc_stg_evnt, DATEDIFF(mindate,maxdate,DAY)+1),

REMOVEFILTERS(fact_doc_stg_evnt[days_in_support],fact_doc_stg_evnt[times_in_hold],fact_doc_stg_evnt[times_in_support],fact_doc_stg_evnt[days_in_hold]))

But this adds extra one day to all rows for the doc_key. What am i doing wrong?

[v-yadongf-msft]

Hi @KV2022 ,

Can you share with me some screenshots of your data after hiding sensitive data?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

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

[KV2022]

 Hi, thanks for your time. I could not take the screenshot in one stretch. I need to  find the difference of dates between min(actl_strt_dttm) and max(actl_end_dttm) for every dim_doc_key and the result should go as a  row in the pivot table. If i calculate this as a measure , it is displayed in the values column of the pivot and messing up the display.

[amitchandak]

@KV2022 , Based on what I got

Sumx(Filter(Table, [ID] = earlier([ID]) ), [Days])

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[KV2022]

I have to create a calculated column in the fact table .

This calculated column should have the values of datediff(mindate,maxdate,day) +1. 

Since there are multiple records for one document key in the fact , 

CALCULATE(sumx(fact_doc_stg_evnt, DATEDIFF(mindate,maxdate,DAY)+1),

REMOVEFILTERS(fact_doc_stg_evnt[days_in_support],fact_doc_stg_evnt[times_in_hold],fact_doc_stg_evnt[times_in_support],fact_doc_stg_evnt[days_in_hold]))

is giving wacky results when plotted in a matrix.

My objective is to find the no of days for every ID, which has to be one value in the row of a matrix visual. So this cant be calculated as a measure, which goes to values section of a matrix.

Can anybody help?

[KV2022]

thanks. But i Overlooked one other scenario in fact tables.

Diff of days between min(start) of all iterations and max(end) of all iterations.

fact table

ID1 st1 iter1 startdate  end date

ID1 st1 iter2 startdate  end date

ID1 st2  iter1 startdate  end date

ID1 st2 iter2 startdate enddate

Now i need the datediff between min(startdate) and max(end date) ..but i need this as a column in fact bcos this has to go in rows of pivot

[KV2022]

Hi this is my data.  I need to display the total Duration = datediff (min(start), max(end), day)+1 =  17 in one row in the matrix bcos evry  dim_doc_key has one row in the display. please help

my objective is to display in the rows section of a matrix display

dim_doc_key    Total Duration 

89780                17

dim_doc_key	dim_proj_stg_key	itrtn_nbr	actl_strt_dttm	actl_end_dttm	Total Duration by fact
89780	33970	4	3/3/2021 15:30	3/4/2021 8:13	17
89780	33127	1	2/16/2021 15:30	2/17/2021 4:54	17
89780	31732	8	3/1/2021 7:24	3/2/2021 4:53	17
89780	31673	2	3/4/2021 18:02	3/4/2021 18:02	17
89780	32816	1	2/20/2021 6:13	2/21/2021 11:56	17
89780	32721	1	2/18/2021 8:00	2/20/2021 6:13	17
89780	31942	2	2/25/2021 11:44	3/1/2021 7:24	17
89780	33195	2	2/21/2021 11:56	2/25/2021 11:44	17
89780	31830	2	2/17/2021 4:54	2/18/2021 8:00	17
89780	32577	2	3/4/2021 8:13	3/4/2021 18:02	17
89780	33969	1	2/16/2021 15:30	2/16/2021 15:30	17
89780	33705	1	2/16/2021 15:30	2/16/2021 15:30	17
89780	33605	2	2/16/2021 0:00	2/16/2021 15:30	17