Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KV2022
Helper II
Helper II

calculated column to return one value in the report

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

12 REPLIES 12
v-yadongf-msft
Community Support
Community Support

Hi @KV2022 .

 

Remove +1 and try again:

CALCULATE(sumx(fact_doc_stg_evntDATEDIFF(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.

 

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

v-yadongf-msft
Community Support
Community Support

Hi @KV2022 ,

 

This is my test table:

vyadongfmsft_0-1663665950983.png

 

Please try following DAX:

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

 

Is this the result you want?

vyadongfmsft_1-1663666053310.png

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.

 

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

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:

vyadongfmsft_0-1663728912718.png

 

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.

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
Community Support
Community Support

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_0-1663662148257.png

KV2022_1-1663662185705.png

 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
Super User
Super User

@KV2022 , Based on what I got

 

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

 

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

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_evntDATEDIFF(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?

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

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_keydim_proj_stg_keyitrtn_nbractl_strt_dttmactl_end_dttmTotal Duration by fact
897803397043/3/2021 15:303/4/2021 8:1317
897803312712/16/2021 15:302/17/2021 4:5417
897803173283/1/2021 7:243/2/2021 4:5317
897803167323/4/2021 18:023/4/2021 18:0217
897803281612/20/2021 6:132/21/2021 11:5617
897803272112/18/2021 8:002/20/2021 6:1317
897803194222/25/2021 11:443/1/2021 7:2417
897803319522/21/2021 11:562/25/2021 11:4417
897803183022/17/2021 4:542/18/2021 8:0017
897803257723/4/2021 8:133/4/2021 18:0217
897803396912/16/2021 15:302/16/2021 15:3017
897803370512/16/2021 15:302/16/2021 15:3017
897803360522/16/2021 0:002/16/2021 15:3017

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors