cancel
Showing results for
Did you mean:
Frequent Visitor

## Value based on last date

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 🙂

1 ACCEPTED SOLUTION
Community Support

Hi @Termenoque ,

1. Create a Dates table.

``````Dates =
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.

5 REPLIES 5
Super User IV

@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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Proud to be a Super User!

Frequent Visitor

@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

JulAug
MTD Average Inbound volume217146
MTD Average Resolved Volume208160
Last Date:17/08/20
Last Open Volume on last date:1186
No of days to clear volume based on Avg Resolved volume7.4Last Open Volume ÷ MTD Average Resolved Volume

Community Support

Hi @Termenoque ,

1. Create a Dates table.

``````Dates =
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.

Frequent Visitor

@amitchandak Thanks for the help - got it to work eventually 🙂

Frequent Visitor

@Icey Thanks for your help - got it to work eventually 🙂

Announcements