Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Folks,
I am working on my first BI project and have been unsuccessful in splicing solutions from various posts that contain portions of what I'm looking to accomplish. Thus far, I've been unable to make the pieces from those posts work to achieve my desired results.
GOAL:
I'm trying to get a column that will calculate the changes in INV by day with various conditions:
NOTE: The change in INV for the P/Ns will be used to calculate the $'s value change further when I bring in the pricing data on a report that has some slicers for accumulated changes by week, month, quarter, etc.
Test File for help
Model:
CALENDAR(snip): Already factors in the Week Day # and an IsHoliday logic.
TBL-Data Raw(Source data will not always have P/N in the same sequence by day, but are unique values within the day's download)
RESULT:
Part of my evolution to this reach out for aid has resulted from learning through other posts here (thank you).
Solved! Go to Solution.
Hello @rwverhovec
i might be missing something but i assumed what you need is only 'Result' value then I guessed the other tables are there for some other reasons (not for finding 'Result' value since 'TBL-Data Raw' doesnt have any calculation related to other tables).
But appart from that, please check if this result match to your need.
basically you need to find the previous value of INV then substract that to current value.
Hi,
This calculated column formula works
Column = if(ISBLANK(LOOKUPVALUE('TBL-Data Raw'[INV],'TBL-Data Raw'[DATE],CALCULATE(MAX('TBL-Data Raw'[DATE]),FILTER('TBL-Data Raw','TBL-Data Raw'[P/N]=EARLIER('TBL-Data Raw'[P/N])&&'TBL-Data Raw'[DATE]<EARLIER('TBL-Data Raw'[DATE]))),'TBL-Data Raw'[P/N],'TBL-Data Raw'[P/N])),BLANK(),'TBL-Data Raw'[INV]-LOOKUPVALUE('TBL-Data Raw'[INV],'TBL-Data Raw'[DATE],CALCULATE(MAX('TBL-Data Raw'[DATE]),FILTER('TBL-Data Raw','TBL-Data Raw'[P/N]=EARLIER('TBL-Data Raw'[P/N])&&'TBL-Data Raw'[DATE]<EARLIER('TBL-Data Raw'[DATE]))),'TBL-Data Raw'[P/N],'TBL-Data Raw'[P/N]))
Hope this helps.
Hi,
This calculated column formula works
Column = if(ISBLANK(LOOKUPVALUE('TBL-Data Raw'[INV],'TBL-Data Raw'[DATE],CALCULATE(MAX('TBL-Data Raw'[DATE]),FILTER('TBL-Data Raw','TBL-Data Raw'[P/N]=EARLIER('TBL-Data Raw'[P/N])&&'TBL-Data Raw'[DATE]<EARLIER('TBL-Data Raw'[DATE]))),'TBL-Data Raw'[P/N],'TBL-Data Raw'[P/N])),BLANK(),'TBL-Data Raw'[INV]-LOOKUPVALUE('TBL-Data Raw'[INV],'TBL-Data Raw'[DATE],CALCULATE(MAX('TBL-Data Raw'[DATE]),FILTER('TBL-Data Raw','TBL-Data Raw'[P/N]=EARLIER('TBL-Data Raw'[P/N])&&'TBL-Data Raw'[DATE]<EARLIER('TBL-Data Raw'[DATE]))),'TBL-Data Raw'[P/N],'TBL-Data Raw'[P/N]))
Hope this helps.
Hello @rwverhovec
i might be missing something but i assumed what you need is only 'Result' value then I guessed the other tables are there for some other reasons (not for finding 'Result' value since 'TBL-Data Raw' doesnt have any calculation related to other tables).
But appart from that, please check if this result match to your need.
basically you need to find the previous value of INV then substract that to current value.
@Irwan
Thanks for the prompt solution. I incorporated the column and verified that everything worked well. I especially appreciate your explanation in the post and comments within the formulas. They help me understand as a new BI user.
I provided a layout of the model because the end goal of this table is to produce a dashboard of results by week, month, quarter, etc. While trying to make it work, I've evolved as I read what others had done but couldn't get past this hurdle. Going to press on and see where the next hurdle is.
-Appreciate you,
Hello @rwverhovec ,
glad to be help and good luck for your project as writting DAX can be challenging.
Also try using DAX provided by @Ashish_Mathur . The DAX is great and might be good for understanding how the DAX works in couple ways.
Thank you.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
22 | |
22 |