cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianhan13
Helper III
Helper III

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 IV
Super User IV

@ianhan13 , 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-La...
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-5c324...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@ianhan13 , 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-La...
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-5c324...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

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

littlemojopuppy
Super User I
Super User I

Hi @ianhan13 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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.