datediff function not giving proper value

[Anonymous]

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.

[v-xicai · Accepted solution]

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]

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]

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