Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

datediff function not giving proper value

Hi ,

 I am converting tableau report in power bi . While converting taleau formula i am facing some issue. The tableau formula is - Total Rig days = datediff('second',{include [Job Number]: min([Date In])}, {INCLUDE [Job Number]: max([Date Out])})

I converted in power bi like this -

Total Rig Day = DATEDIFF({AVERAGEX(SUMMARIZE(sp_ftl_dbm_tfs_summ_v2_sn,sp_ftl_dbm_tfs_summ_v2_sn[Job Number], "Date",min(sp_ftl_dbm_tfs_summ_v2_sn[min_date_in])),[Date]),({AVERAGEX(SUMMARIZE(sp_ftl_dbm_tfs_summ_v2_sn,sp_ftl_dbm_tfs_summ_v2_sn[Job Number],"Date", max(sp_ftl_dbm_tfs_summ_v2_sn[max_date_out])),[Date])}),SECOND) 
But it is not giving the proper result . I tried in measre and calculated column both . While trying with sumx function giving me invalid numerical representation error.
Please help to correct it.
 
Thanks.
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    Open Count=

Var _MinDate = CALCULATE(MIN('sp_ftl_dbm_tfs_summ_v2_sn'[Date in]), ALLEXCEPT('sp_ftl_dbm_tfs_summ_v2_sn', 'sp_ftl_dbm_tfs_summ_v2_sn'[Job number]))

Var _MaxDate = CALCULATE(MAX('sp_ftl_dbm_tfs_summ_v2_sn'[Date Out]), ALLEXCEPT('sp_ftl_dbm_tfs_summ_v2_sn', 'sp_ftl_dbm_tfs_summ_v2_sn'[Job number]))

Return

DATEDIFF(_MinDate, _MaxDate, SECOND)

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Please post example mock data and desired output.  I suspect your DAX expression can be much shorter than how you've started.

     

    Regards,

    Pat

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Data is like this -

       

      Job number   Date in            Date Out          

      17 CG 10       20/07/2019     25/09/2019

      17 Fv  11        22/08/2019     28/10/2019

       

      Total rigs day comes in tableau -          but in power bi 

      14,589                                                    170,296,584

      16,830                                                    186,345,256

       

       

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    Open Count=

Var _MinDate = CALCULATE(MIN('sp_ftl_dbm_tfs_summ_v2_sn'[Date in]), ALLEXCEPT('sp_ftl_dbm_tfs_summ_v2_sn', 'sp_ftl_dbm_tfs_summ_v2_sn'[Job number]))

Var _MaxDate = CALCULATE(MAX('sp_ftl_dbm_tfs_summ_v2_sn'[Date Out]), ALLEXCEPT('sp_ftl_dbm_tfs_summ_v2_sn', 'sp_ftl_dbm_tfs_summ_v2_sn'[Job number]))

Return

DATEDIFF(_MinDate, _MaxDate, SECOND)

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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