Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
Relative noobie here. I have checked many existing posts and videos but I am not getting how to approach the problem.
I have 2 tables as below, with one-to-many from LOADS.load to HOLDINGS.load in order to define the Previous Load given a Load.
And this is what I would like to see in a Matrix or Table:
I realize I need to do something at the Query level (new column or some DAX expression) but I don't understand how I can add a column to my Holdings table that says something like:
Previous Value = HOLDINGS.Value where current.Load = LOADS.Previous Load and current.Holding = HOLDINGS.Holding
And then calculating the Chg would be easy enough (Calculated column).
Please help!
Solved! Go to Solution.
Simple enough if the rule of "previous load" persists; even the LOADS table is redundant. (rule of "previous load": the max Load less than current Load)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
Simple enough if the rule of "previous load" persists; even the LOADS table is redundant. (rule of "previous load": the max Load less than current Load)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
Even though all solutions posted work, I like this one because I don't have to create the Load table and entries - even though I didn't ask for this.
Thanks all for helping me out.
Total = SUM(HOLDINGS[value])
Chg = VAR _prev=CALCULATE([Total],TREATAS(VALUES(LOADS[previous load]),HOLDINGS[load]),REMOVEFILTERS(LOADS[load])) RETURN DIVIDE([Total]-_prev,_prev)
Hi @dbiduk ,
With the given data, create two calculated columns
Previous Load by holding =
CALCULATE (
MAX ( 'Table'[Load] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Holding] ),
'Table'[Load] < EARLIER ( 'Table'[Load] )
)
)
Previous value by holding =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Holding] ),
'Table'[Load] = EARLIER ( 'Table'[Previous Load by holding] )
)
)
Use the second column to create a % change measure.
Note: the look you want for your matrix is not currently achievable out of the box ( will require advanced DAX)
Please see attached pbix for reference.
Actually, this one doesn't need the LOAD table either. I kinda prefer this one becuase it adds the new data as calculated columns, not measures. But, again, I am new so what do I know :-).
Thanks!!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
11 | |
7 | |
6 | |
6 |