Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Termenoque_0-1597931569861.png

 

1 ACCEPTED 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.

MTD3.jpg

 

3. Create relationship between Dates table and your fact table.

MTD4.jpg

 

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

MTD.PNG

MTD2.PNG

 

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.

 

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

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

 

@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

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.

MTD3.jpg

 

3. Create relationship between Dates table and your fact table.

MTD4.jpg

 

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

MTD.PNG

MTD2.PNG

 

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.

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.