Display Only Most Recent Value?

[susannataylor]

Hi all, hoping you can help me out! I'm still pretty new to Power BI and despite finding formulas that seem like the solution I'm still stumped!

Two of the progams I report outcomes on have waitlists. I'd like to have a display at the top of the report showing the most recent waitlist totals. The list I'm pulling from looks like this: 

Week Of	FB Waitlist	PATs Waitlist
6/20/20	2	6
7/20/20	4	8

And of course, I'd like the table/matrix it's displayed in to look like this:

Program	Current Waitlist
FB	4
PATs	8

Thanks in advance!!

[mahoneypat]

You can use an approach like this to get your result

Latest PATs Waitlist =
VAR maxdate =
    MAX ( Table[WeekOf] )
RETURN
    CALCULATE ( MIN ( Table[PATs Waitlist] ), Table[WeekOf] = maxdate )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[susannataylor]

@mahoneypat 

This worked great for just one column (PATs) but when I tried to add the FB column this error message popped up: "A circular dependency was detected: ECS Referrals PR[FB Most Recent Waitlist], ECS Referrals PR[PATs Most Recent Waitlist], ECS Referrals PR[FB Most Recent Waitlist]."

Any ideas?

[mahoneypat]

Just to confirm.  The expression I sent should be used in a measure, not a calculated column.  Can you send your measure for the FB measure so I can see what might cause the circular dependency?

Regards,

Pat

[edhans]

@susannataylor see the PBIX file I linked to. You need to use measures, not calculated columns. Calculated columns will give you these circular dependency issues. One of many reasons to avoid them.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

[edhans · Accepted solution]

@susannataylor ,
This will give you the latest date in your table:

Latest Date = 
CALCULATE(
    MAX('Table'[Week Of]),
    ALL('Table'[Week Of])
)

This will give you the latest value for that date. You didn't specify what you meant by total. If you just mean the total of everything, then stick this measure in a card:

Grand Total = SUM(Table[PATs Waitlist])

But if you want the total for the latest date, then this works fo rthe PATs Waitlist column.

Latest Total = 
CALCULATE(
    SUM('Table'[PATs Waitlist]),
    ALL('Table'[PATs Waitlist]),
    FILTER(
        ALL('Table'[Week Of]),
        'Table'[Week Of] = [Latest Date]
    )
)

 Can you explain the logic of your desired matrix? 

EDIT: I looked at it again, and think I see what you mean. You need to fix your table in Power Query first.

1. Select the first column (week of) in Power Query.
2. On the Transform menu, select Unpivot Other Columns.
3. Rename the columns as desired. You will get this:
   1. 
4. Now create these two measures:

Normalized Latest Date = 
CALCULATE(
    MAX('Normalized Table'[Week Of]),
    ALL('Normalized Table'[Week Of])
)

Normalized Latest Week Total = 
CALCULATE(
    SUM('Normalized Table'[Value]),
    FILTER(
        ALL('Normalized Table'[Week Of]),
        'Normalized Table'[Week Of] = [Normalized Latest Date]
    )
)

You can create this matrix:

See my PBIX file here. You want the "Normalized Table" to work through.

[susannataylor]

Sorry, what I meant by total is that I just want the most recent value displayed. Does that answer your question?

[edhans]

@susannataylor - see my edit above in the first reply.