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.

TcT85's avatar
TcT85
Helper III
4 years ago

Yes, it works!!! Thanks Icey!

 

I added ABS to remove the minus value, not sure if this is the best solution.

Dax Measure = 
VAR Datedifferent= DATEDIFF (
    MAX( PD_PcbProductionData[SEL_LOD_LatestEndDateTime] ),
    [DAX Date Previous2],
    SECOND
)
Return
ABS(Datedifferent)

 

One more question.

 

How can i Sum all the measure values?

Dax Total Measure = CALCULATE(PD_PcbProductionData[Dax Measure])

I tried to sum the measure values, but it doesnt calculate correct.

For some reason it only takes the latest value that is 556 at the image below.

 

 

Icey's avatar
Icey
Community Support
4 years ago

Hi TcT85 ,

 

Try something like this:

DAX Total Measure =
SUMX ( ALLSELECTED ( PD_PcbProductionData[PcbSerialNumber] ), [DAX Measure] )

 

 

Best Regards,

Icey

 

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