Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've seen similar topics but I think my case goes a bit further. I have a database with about 3 million rows and growing of data. There's a lot more to this data, but the relevent (and fuzzed) columns are here:
Columns A thru D are in my data. Column E shows what I'd like to show or calculate with Power BI. I need to apply this logic to the calculation:
LastWeekComp = Lookup where Location = Location Lookup where IAVA = IAVA Lookup where Date = Date -7 days Return Compliant
And so the calculation of that final column would simply be
Difference From Previous Week = Compliant - LastWeekComp
I've seen lots of different suggestions for how to tackle this, and I've tried going down each one. I just end up confusing myself. Can any of you smart smart people help me down the right path? Thank you so much for any insights!
Solved! Go to Solution.
Hi @squiddly
Please try adding this calculated column to your table. Just replace the text 'Table1' with your own table name. Remember to format the column as approprate.
Column = VAR LastWeekComp = SUMX( FILTER( 'Table1', 'Table1'[Location] = EARLIER('Table1'[Location]) && 'Table1'[Date] = EARLIER('Table1'[Date])-7 && 'Table1'[CVE] = EARLIER('Table1'[CVE]) ) ,'Table1'[Compliant]) VAR Compliant = 'Table1'[Compliant] RETURN Compliant - LastWeekComp
Hi @squiddly
Please try adding this calculated column to your table. Just replace the text 'Table1' with your own table name. Remember to format the column as approprate.
Column = VAR LastWeekComp = SUMX( FILTER( 'Table1', 'Table1'[Location] = EARLIER('Table1'[Location]) && 'Table1'[Date] = EARLIER('Table1'[Date])-7 && 'Table1'[CVE] = EARLIER('Table1'[CVE]) ) ,'Table1'[Compliant]) VAR Compliant = 'Table1'[Compliant] RETURN Compliant - LastWeekComp
Thank you!! It works marvelously, now I can use slicer on this thing.
Phil, you are so awesome. I took this home to work on over a 3-day weekend and I was getting nowhere.
That code worked perfectly for my sample. Will be implementing it this week at work. Thanks so much!
First foremost you need calendar table in your data model for any time based calculation, either it is previous month/week/year etc. You need to start with that then go from there. There are many posts on how to create calendar table in PowerBI which can get started you on that.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |