Forum Discussion
Display Only Most Recent Value?
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!!
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.- Select the first column (week of) in Power Query.
- On the Transform menu, select Unpivot Other Columns.
- Rename the columns as desired. You will get this:
-
- 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.
7 Replies
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
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?
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
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.- Select the first column (week of) in Power Query.
- On the Transform menu, select Unpivot Other Columns.
- Rename the columns as desired. You will get this:
-
- 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.
Sorry, what I meant by total is that I just want the most recent value displayed. Does that answer your question?
susannataylor - see my edit above in the first reply.
