cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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/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

Ian

1 ACCEPTED SOLUTION
Super User

@ianhan13 , Unpivot the data .

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
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
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...
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

4 REPLIES 4
Super User

@ianhan13 , Unpivot the data .

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
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
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...
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Helper III

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

Helper III

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

Community Champion

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors