Forum Discussion

KV2022's avatar
KV2022
Icon for Helper II rankHelper II
4 years ago

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...
Need Help
v-yadongf-msft's avatar
v-yadongf-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    KV2022
    Icon for Helper II rankHelper II
    4 years ago

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

    • v-yadongf-msft's avatar
      v-yadongf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        KV2022
        Icon for Helper II rankHelper II
        4 years ago

        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?