Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Earliest Date Calculations

I need a quick bit of help on Power BI. Consider a table showing Customer/Contracts and the dates those contracts were signed – a bit like this:   Customer/Contract Date Sky-ABC123 2...
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Sure,

    CALCULATED modifies Filter Context
    TREATAS applies context on Table Date and filters it to only __minDate Value

    KEEPFILTERS ensures the value is not calculated for 01/06/2019 as no new contracts there.

     

    You could simplify this syntax  and remove TREATAS like below

    VAR __minDate = 
CALCULATE( 
    MIN( 'Table'[Date] ), 
    ALL( 'Table' ), 
    VALUES( 'Table'[Customer/Contract] ) 
)
RETURN 
CALCULATE(
    COUNTROWS( 'Table' ),
    KEEPFILTERS( 'Table'[Date] = __minDate )
) + 0

     

    Learn more about

    TREATAS - https://www.sqlbi.com/blog/marco/2017/11/25/using-treatas-in-place-of-in-in-dax/
    KEEPFILTERS - https://www.sqlbi.com/articles/using-keepfilters-in-dax/


    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.


Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

 

Try this

Measure = 
VAR __minDate = 
CALCULATE( 
    MIN( 'Table'[Date] ), 
    ALL( 'Table' ), 
    VALUES( 'Table'[Customer/Contract] ) 
)
RETURN 
CALCULATE(
    COUNTROWS( 'Table' ),
    KEEPFILTERS( TREATAS( { __minDate }, 'Table'[Date] ) )
) + 0
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Mariusz  Thanks for your quick reply, Could you please explain little bit in details how we are calcualting this measure, especially this part of the formula, I am quite new in DAX and trying to understand what does this part of DAX is:

CALCULATE(
    COUNTROWS( 'Table' ),
    KEEPFILTERS( TREATAS( { __minDate }, 'Table'[Date] ) )
) + 0

 

 

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    Sure,

    CALCULATED modifies Filter Context
    TREATAS applies context on Table Date and filters it to only __minDate Value

    KEEPFILTERS ensures the value is not calculated for 01/06/2019 as no new contracts there.

     

    You could simplify this syntax  and remove TREATAS like below

    VAR __minDate = 
CALCULATE( 
    MIN( 'Table'[Date] ), 
    ALL( 'Table' ), 
    VALUES( 'Table'[Customer/Contract] ) 
)
RETURN 
CALCULATE(
    COUNTROWS( 'Table' ),
    KEEPFILTERS( 'Table'[Date] = __minDate )
) + 0

     

    Learn more about

    TREATAS - https://www.sqlbi.com/blog/marco/2017/11/25/using-treatas-in-place-of-in-in-dax/
    KEEPFILTERS - https://www.sqlbi.com/articles/using-keepfilters-in-dax/


    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.


    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      MariuszThanks a lot for your extended help. I appreciate your prompt reply and taking out time to answer it.