Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
There is a weekly report with ongoing projects. I am combining this data into table Backlog. There is a column Report Date which today has three values:
One of the columns is called Corr Completion Date.
I want to create a matrix that will calculate the difference between these dates across different report dates.
Report Date | Project # | Corr Completion Date |
11/12/2020 | 12345678 | 31/12/2020 |
04/12/2020 | 12345678 | 15/12/2020 |
Example:
The User selects Report Date = 11/12/2020 with a slicer and Project #12345678.
He should see the difference between Corr Completion Dates, so the value = 16 (16 days difference).
Does anyone know how to do it?
thank you,
Marek
Solved! Go to Solution.
Please try this measure expression in a table visual with your Project # and Report Date columns
Days Change Previous Report =
VAR vThisCompDate =
MIN ( Projects[Corr Completion Date] )
VAR vThisReportDate =
MIN ( Projects[Report Date] )
VAR vPrevCompDate =
CALCULATE (
LASTNONBLANKVALUE (
Projects[Report Date],
MIN ( Projects[Corr Completion Date] )
),
ALLEXCEPT (
Projects,
Projects[Project #]
),
Projects[Report Date] < vThisReportDate
)
RETURN
DATEDIFF (
vPrevCompDate,
vThisCompDate,
DAY
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please use this expression in place of your current expression (but uses it).
NewMeasure = SUMX(SUMMARIZE(Projects, Projects[Project], Projects[Job Site]), [Days Delta WoW])
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Actually, I have one more question. do you know how can I fix the total value of days difference? The below screen shows the problem.
I have broken down your formula (the one you suggested above) to variables that you can see below as measures (columns in the matrix). I was trying to use something like SUMX, but it didn't work...
I would expect the last column to show 750, not 379.
Works like a charm - thank you very much.
Please try this measure expression in a table visual with your Project # and Report Date columns
Days Change Previous Report =
VAR vThisCompDate =
MIN ( Projects[Corr Completion Date] )
VAR vThisReportDate =
MIN ( Projects[Report Date] )
VAR vPrevCompDate =
CALCULATE (
LASTNONBLANKVALUE (
Projects[Report Date],
MIN ( Projects[Corr Completion Date] )
),
ALLEXCEPT (
Projects,
Projects[Project #]
),
Projects[Report Date] < vThisReportDate
)
RETURN
DATEDIFF (
vPrevCompDate,
vThisCompDate,
DAY
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |