Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION

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.


View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

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
Not applicable

@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

 

 

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors