Forum Discussion

Termenoque's avatar
Termenoque
Frequent Visitor
6 years ago
Solved

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 eq...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
Termenoque
Frequent Visitor
6 years ago

@amitchandak As requested, here is the table and hopefully clearer explannation of what I'm trying to achieve.

DateResolvedCountryInboundOpenResolved
26/07/20DE15870
26/07/20UK4318372
27/07/20DE137635121
27/07/20UK3401960361
28/07/20DE151619175
28/07/20UK4232013310
29/07/20DE133612128
29/07/20UK4732110351
30/07/20DE129633114
30/07/20UK3242045388
31/07/20DE121598155
31/07/20UK3311941389
01/08/20UK2018478
02/08/20UK1818580
03/08/20DE148593126
03/08/20UK3651960364
04/08/20DE146596116
04/08/20UK3121884349
05/08/20DE109557193
05/08/20UK2671827317
06/08/20DE160583113
06/08/20UK2811808270
07/08/20DE130583132
07/08/20UK2841753282
08/08/20DE15740
08/08/20UK1916787
09/08/20UK1516846
10/08/20DE124591125
10/08/20UK3251736322
11/08/20DE110570121
11/08/20UK3261722302
12/08/20DE138560133
12/08/20UK3391663297
13/08/20DE13257596
13/08/20UK3211632294
14/08/20DE125598112
14/08/20UK2331472384
15/08/20DE15811
15/08/20UK713983
16/08/20DE15800
16/08/20UK213904
17/08/20DE37462169
17/08/20UK271186310

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

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      Termenoque
      Frequent Visitor
      6 years ago

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

    • Termenoque's avatar
      Termenoque
      Frequent Visitor
      6 years ago

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