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 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 🙂

 

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.

     

5 Replies

    • 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.