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.
qConsider the below table:
id | rag | date |
101 | red | 01/06/2020 |
101 | red | 02/06/2020 |
103 | red | 01/06/2020 |
103 | green | 02/06/2020 |
103 | green | 05/06/2020 |
101 | green | 05/06/2020 |
I'm looking to create two measures:
Desired outcome for 1
count_prior | |||
red | amber | green | |
101 | 2 | ||
103 | 1 | 1 |
Desired outcome for 2
count_latest | |||
red | amber | green | |
101 | 1 | ||
103 | 1 |
Thank you in advance
Solved! Go to Solution.
Here are two expressions that should work.
Count Latest =
VAR __maxdate =
CALCULATE ( MAX ( 'Table'[date] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE ( COUNT ( 'Table'[id] ), 'Table'[date] = __maxdate )
Count Prior =
VAR __maxdate =
CALCULATE ( MAX ( 'Table'[date] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE ( COUNT ( 'Table'[id] ), 'Table'[date] < __maxdate )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here are two expressions that should work.
Count Latest =
VAR __maxdate =
CALCULATE ( MAX ( 'Table'[date] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE ( COUNT ( 'Table'[id] ), 'Table'[date] = __maxdate )
Count Prior =
VAR __maxdate =
CALCULATE ( MAX ( 'Table'[date] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE ( COUNT ( 'Table'[id] ), 'Table'[date] < __maxdate )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |