Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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
@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.
@MariuszThanks a lot for your extended help. I appreciate your prompt reply and taking out time to answer it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |