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.
Hi All, Struggling to find a logic to create a measure to identify and return only values which are new compared to previous week. below is the sample data. As show in the below table measure should only return highlighted in bold italic.
Date | ID | Name | Group |
21/11/2022 | F046 | Damian | A |
21/11/2022 | F047 | Daniel | A |
21/11/2022 | F048 | David | A |
21/11/2022 | F049 | Debbie | A |
21/11/2022 | F050 | Despina | A |
21/11/2022 | F051 | Elke | B |
21/11/2022 | F052 | Erikalyn | B |
21/11/2022 | F053 | Felicity | B |
21/11/2022 | F054 | Fiona | C |
21/11/2022 | F055 | Gary | D |
28/11/2022 | F046 | Damian | A |
28/11/2022 | F047 | Daniel | A |
28/11/2022 | F048 | David | A |
28/11/2022 | F049 | Debbie | A |
28/11/2022 | F050 | Despina | A |
28/11/2022 | F051 | Elke | B |
28/11/2022 | F052 | Erikalyn | B |
28/11/2022 | F053 | Felicity | B |
28/11/2022 | F054 | Fiona | C |
28/11/2022 | F055 | Gary | D |
28/11/2022 | F056 | James | A |
28/11/2022 | F054 | Fiona | B |
28/11/2022 | F055 | Gary | B |
Solved! Go to Solution.
Hi @Anonymous ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create a measure as below
Flag =
VAR _selid =
SELECTEDVALUE ( 'Table'[ID] )
VAR _selname =
SELECTEDVALUE ( 'Table'[Name] )
VAR _selgroup =
SELECTEDVALUE ( 'Table'[Group] )
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _count =
CALCULATE (
COUNT ( 'Table'[ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = _selid
&& 'Table'[Name] = _selname
&& 'Table'[Group] = _selgroup
&& 'Table'[Date] < _maxdate
)
)
RETURN
IF ( ISBLANK ( _count ), 1, 0 )
2. Create a table visual and apply the visual-level filter with the condition(Flag is 1)
Best Regards
Hi @Anonymous ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create a measure as below
Flag =
VAR _selid =
SELECTEDVALUE ( 'Table'[ID] )
VAR _selname =
SELECTEDVALUE ( 'Table'[Name] )
VAR _selgroup =
SELECTEDVALUE ( 'Table'[Group] )
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _count =
CALCULATE (
COUNT ( 'Table'[ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = _selid
&& 'Table'[Name] = _selname
&& 'Table'[Group] = _selgroup
&& 'Table'[Date] < _maxdate
)
)
RETURN
IF ( ISBLANK ( _count ), 1, 0 )
2. Create a table visual and apply the visual-level filter with the condition(Flag is 1)
Best Regards
Hi,
Here is one way to do this:
Create the following dax:
This will return the desired values + values which don't have value 3 weeks ago (since there is no data before this):
Now you can create maxdate filter measure or use relative date filtering:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |