topic Re: Earliest Date Calculations in DAX Commands and Tips

Earliest Date Calculations

Anonymous — Fri, 03 Jan 2020 09:53:00 GMT

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	2018-06
Sky-ABC124	2018-06
Sky-ABC123	2019-06

The problem is that we need to identify how many new were signed in each month. So in the above table Sky signed 2 new contracts in June 2018 but zero new ones in June 2019 as that is a renewal. Required output is below. Really we just needs a way to distinguish renewals from new – which can be done by finding the earliest date for each distinct customer/contract. I could manage this in SQL easily enough but have no idea how to do it in PowerBI. Can someone guide how to do this in PowerBI?

Month	New Contracts
2018-06	2
2019-06	0

Re: Earliest Date Calculations

Mariusz — Fri, 03 Jan 2020 10:26:05 GMT

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.

Re: Earliest Date Calculations

Anonymous — Fri, 03 Jan 2020 10:36:06 GMT

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

Re: Earliest Date Calculations

Mariusz — Fri, 03 Jan 2020 11:33:15 GMT

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.

Re: Earliest Date Calculations

Anonymous — Fri, 03 Jan 2020 11:44:53 GMT

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