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

Don'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.

Reply
Anonymous
Not applicable

Extracting / Plotting values in the last column

Hi everyone

I have a table of account balances by date, in the following format:

 

Account 01/01/202102/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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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:

ianhan13_1-1610045969952.png

 

The actual text enlarged is

ianhan13_2-1610046302330.png

 and the error message 

 

ianhan13_3-1610046343183.png

 

Many thanks

 

Ian

Anonymous
Not applicable

Having spotted the missing ' in line 2, I still get unexpected expression for [Value] and [Date] on line 2.

littlemojopuppy
Community Champion
Community Champion

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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