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,
I have a set of projects where the project status and key dates is reported each month. I am trying to write a measure that will quickly show the projects who have had date changes between the latest month reporting period and the one previously. In the example below, project A has had a change in construciton date moving from Aug-Sept and project C has had a change in commissioning date.
Will I need to write a measure for each Date that I am interested in? I'm also keen that this can be dynmaic as possible so that when a new month is added the measure still compares the latest reporting month to the previous month.
Thanks in advance!
Project | Output | Construction Start Date | Commissioning Date | Reporting Period |
A | 50 | 01/01/2023 | 01/06/2023 | Aug-21 |
B | 25 | 01/04/2023 | 07/04/2023 | Aug-21 |
C | 50 | 01/05/2023 | 01/07/2023 | Aug-21 |
A | 50 | 01/02/2023 | 01/06/2023 | Sep-21 |
B | 25 | 01/04/2023 | 07/04/2023 | Sep-21 |
C | 50 | 01/05/2023 | 01/06/2023 | Sep-21 |
Solved! Go to Solution.
Hi @GJUDGE ,
Depending on how you are making the filter of the month but in this case I have added a disconnected table for the filtering of the month of reporting and did the following measure:
Has Changes =
IF (
COUNTROWS (
SUMMARIZE (
FILTER (
'Table',
'Table'[Reporting Period] <= MAX ( 'Calendar'[Date] )
&& 'Table'[Reporting Period]
>= DATE ( YEAR ( MIN ( 'Calendar'[Date] ) - 1 ), MONTH ( MIN ( 'Calendar'[Date] ) - 1 ), 1 )
),
'Table'[Project],
'Table'[Commissioning Date],
'Table'[Construction Start Date]
)
) > 1,
"Project has changes"
)
Final result below
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @GJUDGE ,
I was thinnking about the issue and you can use this measure to reflect what is changing in a single measure:
Has Date Changes =
VAR TEmpTable =
SUMMARIZE (
FILTER (
'Table',
'Table'[Reporting Period] <= MAX ( 'Calendar'[Date] )
&& 'Table'[Reporting Period]
>= DATE ( YEAR ( MIN ( 'Calendar'[Date] ) - 1 ), MONTH ( MIN ( 'Calendar'[Date] ) - 1 ), 1 )
),
'Table'[Project],
"Commissioning", DISTINCTCOUNT ( 'Table'[Commissioning Date] ),
"Starting Date", DISTINCTCOUNT ( 'Table'[Construction Start Date] )
)
VAR Comissiningdate =
IF (
COUNTROWS ( FILTER ( TEmpTable, [Commissioning] > 1 ) ) > 0,
"Change in commission date"
)
VAR StartDate =
IF (
COUNTROWS ( FILTER ( TEmpTable, [Starting Date] > 1 ) ) > 0,
"Change in Starting date"
)
RETURN
Comissiningdate
& IF (
OR ( ISBLANK ( Comissiningdate ), ISBLANK ( StartDate ) ),
BLANK (),
" | "
) & StartDate
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi, GJUDGE
You can try the following methods:
CSD changes =
IF (
CALCULATE (
MIN ( 'Table'[Construction Start Date] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Project] = SELECTEDVALUE ( 'Table'[Project] )
)
)
= SELECTEDVALUE ( 'Table'[Construction Start Date] ),
BLANK (),
SELECTEDVALUE ( 'Table'[Project] )
)
CD changes =
IF (
CALCULATE (
MIN ( 'Table'[Commissioning Date] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Project] = SELECTEDVALUE ( 'Table'[Project] )
)
)
= SELECTEDVALUE ( 'Table'[Commissioning Date] ),
BLANK (),
SELECTEDVALUE ( 'Table'[Project] )
)
2.The output results of the two measures are shown in the figure:
Best Regards,
Charlotte Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @GJUDGE ,
Depending on how you are making the filter of the month but in this case I have added a disconnected table for the filtering of the month of reporting and did the following measure:
Has Changes =
IF (
COUNTROWS (
SUMMARIZE (
FILTER (
'Table',
'Table'[Reporting Period] <= MAX ( 'Calendar'[Date] )
&& 'Table'[Reporting Period]
>= DATE ( YEAR ( MIN ( 'Calendar'[Date] ) - 1 ), MONTH ( MIN ( 'Calendar'[Date] ) - 1 ), 1 )
),
'Table'[Project],
'Table'[Commissioning Date],
'Table'[Construction Start Date]
)
) > 1,
"Project has changes"
)
Final result below
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |