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([Dat...
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.

     

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