Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to understand the most efficient way to look up changes between cell values within an appended query.
Background: Each month a new excel file is appended to create a 'Combined' query and I would like to identify when there has been changes to key data fields between months.
Example shown below: Blue Columns represent the Appended Query and im looking for a formula that would work to replicate the Orange Columns.
Can anyone help! Thanks!
Solved! Go to Solution.
Hi @RCM ,
It's my pleasure!
You can create another calculated column.
Last month value =
MAXX (
FILTER (
'Table',
'Table'[Record ID] = EARLIER ( 'Table'[Record ID] )
&& MONTH ( 'Table'[Month] )
= MONTH ( EARLIER ( 'Table'[Month] ) ) - 1
),
'Table'[Value]
)
Result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yanjiang-msft - Looking for a bit more advice on this!
The code provided seems to work however as its based on Months only, in January (01) its not finding December (12) as i think the formula is looking at month number only.
Is there any way of expanding this to work for Year as well?
Thanks!
Hi @RCM ,
According to your description, here's my soluton, create a calculated column.
Change =
VAR _Pre =
MAXX (
FILTER (
'Table',
'Table'[Record ID] = EARLIER ( 'Table'[Record ID] )
&& MONTH ( 'Table'[Month] )
= MONTH ( EARLIER ( 'Table'[Month] ) ) - 1
),
'Table'[Value]
)
RETURN
IF ( _Pre = BLANK (), "N/A", IF ( [Value] = _Pre, "N", "Y" ) )
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yanjiang-msft This is excellent,thank you for your help. One last part to solve is how to get last months record value, showing against this months record. Any advice on how i would be able to calculate the last months value column?
Hi @RCM ,
It's my pleasure!
You can create another calculated column.
Last month value =
MAXX (
FILTER (
'Table',
'Table'[Record ID] = EARLIER ( 'Table'[Record ID] )
&& MONTH ( 'Table'[Month] )
= MONTH ( EARLIER ( 'Table'[Month] ) ) - 1
),
'Table'[Value]
)
Result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@RCM , refer if DAX append method can help
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
@amitchandak Afraid not. The data structure I am okay with. This will involve appending Monthly versions of an Excel. I'm struggling when attempting to lookup what the previous month was against the same ID to understand if a change has been made.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
61 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |