Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi everyone
I have a table of account balances by date, in the following format:
Account | 01/01/2021 | 02/01/2021 |
A | £20 | £34 |
B | £23 | £45 |
C | £34 | £56 |
I would like to populate a matrix with the values for the latest date - ie those in the last column. At the moment if I populate the matrix it uses the sum of all dates for each account.
Similarly if I plot the account value over time I only have the option to plot the sum for all periods for each account not the values on individual days.
As a second step, probably too ambitious for me at the moment, I would like to compute the change in value of each account over the last day/week/month/year.
This will be complicated by the fact that the data is only stored monthly prior to this calendar year, but I am happy to use this monthly value as the average for the month to get things moving.
Any help would be very much appreciated
Kind regrads
Ian
Solved! Go to Solution.
@Anonymous , Unpivot the data .
https://radacad.com/pivot-and-unpivot-with-power-bi
https://youtu.be/2HjkBtxSM0g
Assume it will create date and value column
Create a measure like for last date
Meausre =
var _max = maxx(allselected(Table), Table[Date])
Return
calculate(Sum(Table[Value]), filter(Table, Table[Date] =_max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
rest
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9
@Anonymous , Unpivot the data .
https://radacad.com/pivot-and-unpivot-with-power-bi
https://youtu.be/2HjkBtxSM0g
Assume it will create date and value column
Create a measure like for last date
Meausre =
var _max = maxx(allselected(Table), Table[Date])
Return
calculate(Sum(Table[Value]), filter(Table, Table[Date] =_max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
rest
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9
Perfect many thanks I just need to bug you with for the idiots guide to syntax within the measure.
Unpivot went well, but I have made quite a few 🙂 attempts to enter the code as descirbed before bothering you again but I get an error message that the syntax is wrong.
The screen gives me:
The actual text enlarged is
and the error message
Many thanks
Ian
Having spotted the missing ' in line 2, I still get unexpected expression for [Value] and [Date] on line 2.
Hi @Anonymous going to suggest that you unpivot those columns in Power Query to create a date field instead. Then it should be fairly easy to do the things you indicated.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
84 | |
64 | |
52 | |
49 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |