Forum Discussion

TcT85's avatar
TcT85
Helper III
4 years ago
Solved

Direct Query compatible DAX calculation on time difference

Hi,   Need some help with this DAX formula.   Im using direct query and I have tried to some dax formula that was not compatible with Direct Query. I need to calculate the minimum cycle time for...
Need help with DAX
  • Icey's avatar
    Icey
    4 years ago

    Hi TcT85 ,

     

    Please check if this is what you want:

    DAX Date Previous1 =
VAR CurDate_ =
    MAX ( PD_PcbProductionData[Finished Date] )
VAR CurProduct_ =
    MAX ( PD_PcbProductionData[Product] )
RETURN
    CALCULATE (
        MAX ( PD_PcbProductionData[Finished Date] ),
        PD_PcbProductionData[Product] = CurProduct_,
        PD_PcbProductionData[Finished Date] < CurDate_,
        ALLSELECTED ( PD_PcbProductionData )
    )

     

    EARLIER function is mostly used in the context of calculated columns. It is not supported in this scenario.

     

     

    Best Regards,

    Icey

     

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

  • Icey's avatar
    Icey
    4 years ago

    Hi TcT85 ,

     

    Use MAX() / MIN() function like so:

    Measure =
DATEDIFF (
    MAX ( PD_PcbProductionData[Finished Date] ),
    [DAX Date Previous1],
    SECOND
)

     

    Best Regards,

    Icey

     

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

amitchandak's avatar
amitchandak
Super User
4 years ago

TcT85 , can you explain the calculation?

TcT85's avatar
TcT85
Helper III
4 years ago

Hi,

 

I followed microsoft suggestion to first create this colum

DAX Date Previous1 =
CALCULATE (
MAX ( PD_PcbProductionData[SEL_LOD_LatestStartDateTime] ),
ALLEXCEPT ( PD_PcbProductionData, PD_PcbProductionData[PcbSerialNumber] ),
PD_PcbProductionData[SEL_LOD_LatestStartDateTime] < EARLIER ( 'PD_PcbProductionData'[SEL_LOD_LatestStartDateTime] ))
 
But here it says Calculate is not allowed in a DAX expression for Direct query model.
 
If previous column worked i would try to follow up with this secondary dax formula.
 
Date Diff in Days =
IF (
ISBLANK ( 'View Name'[Date Previous] ),
1,
DATEDIFF (
'View Name'[Date Previous],
'View Name'[Date],
DAY
)
)

But I would replace DAY with Second

 

Not sure if this would work though

 

 

 

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi TcT85 ,

     

    Please check if this is what you want:

    DAX Date Previous1 =
VAR CurDate_ =
    MAX ( PD_PcbProductionData[Finished Date] )
VAR CurProduct_ =
    MAX ( PD_PcbProductionData[Product] )
RETURN
    CALCULATE (
        MAX ( PD_PcbProductionData[Finished Date] ),
        PD_PcbProductionData[Product] = CurProduct_,
        PD_PcbProductionData[Finished Date] < CurDate_,
        ALLSELECTED ( PD_PcbProductionData )
    )

     

    EARLIER function is mostly used in the context of calculated columns. It is not supported in this scenario.

     

     

    Best Regards,

    Icey

     

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

    • TcT85's avatar
      TcT85
      Helper III
      4 years ago

      Hi Icey,

       

      Thanks for the DAX formula.

       

      This is the key for me to calculate the cycle time.

       

      When i use Datediff to get the cycle time number, it says that function calculate is not allowed for a new column. in direct query model.

       

       

       

      • Icey's avatar
        Icey
        Community Support
        4 years ago

        Hi TcT85 ,

         

        Use MAX() / MIN() function like so:

        Measure =
DATEDIFF (
    MAX ( PD_PcbProductionData[Finished Date] ),
    [DAX Date Previous1],
    SECOND
)

         

        Best Regards,

        Icey

         

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