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'm trying to get the last value (Open ticket volume) based on the last date of the table but not getting anywhere fast. I have tried creating var measures and whilst i get a result it doesn't equal the last value.
In the table below I'm trying to get the last open value of 1186 based on the last date, so that I can then divide it by another measure of average of resolved volume MTD.
Any help you can provide would be very welcome - this one is giving me a sore head. Solution has to be compatible with PBI Server Desktop (Jan release) not PBI Service. Thank you 🙂
Solved! Go to Solution.
Hi @Termenoque ,
Please check:
1. Create a Dates table.
Dates =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 6, 1 ), DATE ( 2020, 9, 30 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"MonthName", FORMAT ( [Date], "MMM" )
)
2. Sort "MonthName" column by "Month" column.
3. Create relationship between Dates table and your fact table.
4. Create measures.
MTD Average Inbound volume =
TOTALMTD ( AVERAGE ( 'Table'[Inbound] ), 'Dates'[Date] )
MTD Average Resolved Volume =
TOTALMTD ( AVERAGE ( 'Table'[Resolved] ), Dates[Date] )
Last Date =
VAR LastDate_ =
CALCULATE ( MAX ( 'Table'[DateResolved] ), ALLSELECTED ( 'Table' ) )
VAR LastDateMonth =
MONTH ( LastDate_ )
RETURN
IF ( MAX ( Dates[Month] ) = LastDateMonth, LastDate_ )
Last Open Volume on last date =
IF (
[Last Date] <> BLANK (),
CALCULATE (
MAX ( 'Table'[Open] ),
FILTER ( 'Table', 'Table'[DateResolved] = [Last Date] )
)
)
No of days to clear volume based on Avg Resolved volume =
[Last Open Volume on last date] / [MTD Average Resolved Volume]
5. Create a Matrix visual and set value "show on rows".
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Termenoque , the number is not very clear with data you provided. can you explain with example
Can you share sample data and sample output in a table format?
in case you have open / close date. Then refer
@amitchandak As requested, here is the table and hopefully clearer explannation of what I'm trying to achieve.
DateResolved | Country | Inbound | Open | Resolved |
26/07/20 | DE | 1 | 587 | 0 |
26/07/20 | UK | 43 | 1837 | 2 |
27/07/20 | DE | 137 | 635 | 121 |
27/07/20 | UK | 340 | 1960 | 361 |
28/07/20 | DE | 151 | 619 | 175 |
28/07/20 | UK | 423 | 2013 | 310 |
29/07/20 | DE | 133 | 612 | 128 |
29/07/20 | UK | 473 | 2110 | 351 |
30/07/20 | DE | 129 | 633 | 114 |
30/07/20 | UK | 324 | 2045 | 388 |
31/07/20 | DE | 121 | 598 | 155 |
31/07/20 | UK | 331 | 1941 | 389 |
01/08/20 | UK | 20 | 1847 | 8 |
02/08/20 | UK | 18 | 1858 | 0 |
03/08/20 | DE | 148 | 593 | 126 |
03/08/20 | UK | 365 | 1960 | 364 |
04/08/20 | DE | 146 | 596 | 116 |
04/08/20 | UK | 312 | 1884 | 349 |
05/08/20 | DE | 109 | 557 | 193 |
05/08/20 | UK | 267 | 1827 | 317 |
06/08/20 | DE | 160 | 583 | 113 |
06/08/20 | UK | 281 | 1808 | 270 |
07/08/20 | DE | 130 | 583 | 132 |
07/08/20 | UK | 284 | 1753 | 282 |
08/08/20 | DE | 1 | 574 | 0 |
08/08/20 | UK | 19 | 1678 | 7 |
09/08/20 | UK | 15 | 1684 | 6 |
10/08/20 | DE | 124 | 591 | 125 |
10/08/20 | UK | 325 | 1736 | 322 |
11/08/20 | DE | 110 | 570 | 121 |
11/08/20 | UK | 326 | 1722 | 302 |
12/08/20 | DE | 138 | 560 | 133 |
12/08/20 | UK | 339 | 1663 | 297 |
13/08/20 | DE | 132 | 575 | 96 |
13/08/20 | UK | 321 | 1632 | 294 |
14/08/20 | DE | 125 | 598 | 112 |
14/08/20 | UK | 233 | 1472 | 384 |
15/08/20 | DE | 1 | 581 | 1 |
15/08/20 | UK | 7 | 1398 | 3 |
16/08/20 | DE | 1 | 580 | 0 |
16/08/20 | UK | 2 | 1390 | 4 |
17/08/20 | DE | 37 | 462 | 169 |
17/08/20 | UK | 27 | 1186 | 310 |
What we are trying to achieve in PBI | Jul | Aug | |||
MTD Average Inbound volume | 217 | 146 | |||
MTD Average Resolved Volume | 208 | 160 | |||
Last Date: | 17/08/20 | ||||
Last Open Volume on last date: | 1186 | ||||
No of days to clear volume based on Avg Resolved volume | 7.4 | Last Open Volume ÷ MTD Average Resolved Volume |
Hi @Termenoque ,
Please check:
1. Create a Dates table.
Dates =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 6, 1 ), DATE ( 2020, 9, 30 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"MonthName", FORMAT ( [Date], "MMM" )
)
2. Sort "MonthName" column by "Month" column.
3. Create relationship between Dates table and your fact table.
4. Create measures.
MTD Average Inbound volume =
TOTALMTD ( AVERAGE ( 'Table'[Inbound] ), 'Dates'[Date] )
MTD Average Resolved Volume =
TOTALMTD ( AVERAGE ( 'Table'[Resolved] ), Dates[Date] )
Last Date =
VAR LastDate_ =
CALCULATE ( MAX ( 'Table'[DateResolved] ), ALLSELECTED ( 'Table' ) )
VAR LastDateMonth =
MONTH ( LastDate_ )
RETURN
IF ( MAX ( Dates[Month] ) = LastDateMonth, LastDate_ )
Last Open Volume on last date =
IF (
[Last Date] <> BLANK (),
CALCULATE (
MAX ( 'Table'[Open] ),
FILTER ( 'Table', 'Table'[DateResolved] = [Last Date] )
)
)
No of days to clear volume based on Avg Resolved volume =
[Last Open Volume on last date] / [MTD Average Resolved Volume]
5. Create a Matrix visual and set value "show on rows".
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |