Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rwverhovec
Frequent Visitor

Change in INV values by day: Data excludes weekends and holidays

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:

  1. Excluding weekends (Sat, Sun) and our Company Holidays.  TBL-Data Raw will not have any data for those periods. 
  2. Also, the resulting change should not reflect the starting day's value (E.g., P/N A's change on the first day should be zero or blank and not 26).

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:
Model View.png

CALENDAR(snip):
 Already factors in the Week Day # and an IsHoliday logic.
Calendar.png
 
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)

TBL Data Raw.png

 

RESULT:  

Expected Result.png

Part of my evolution to this reach out for aid has resulted from learning through other posts here (thank you).  

2 ACCEPTED SOLUTIONS
Irwan
Solution Specialist
Solution Specialist

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.

Irwan_0-1719364212699.png

 

basically you need to find the previous value of INV then substract that to current value.

 

Result =
var _PreviousIndex = MAXX(FILTER('TBL-Data Raw','TBL-Data Raw'[Index]<EARLIER('TBL-Data Raw'[Index])&&'TBL-Data Raw'[P/N]=EARLIER('TBL-Data Raw'[P/N])),'TBL-Data Raw'[Index]) //find previous index with same date and same P/N
var _ValueCalc = MAXX(FILTER('TBL-Data Raw','TBL-Data Raw'[Index]=_PreviousIndex),'TBL-Data Raw'[INV]) //find value of INV based on located index in _PreviousIndex
Return
IF(
    'TBL-Data Raw'[DATE]=MIN('TBL-Data Raw'[DATE]), //conditional if to make Result zero for all first date
    0,
    'TBL-Data Raw'[INV]-_ValueCalc
)
 
_PreviousIndex is used for looking index for same P/N with previous date (seems direct MAXX to INV value will return into inaccurate INV since you have jump in date which is Saturday and Sunday).
_ValueCalc is for finding INV value based on index that was calculated in _PreviousIndex.
The conditional if is there to zero those first date value as you need.
 
Hope this will help you.
Thank you.

View solution in original post

Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1719374151725.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1719374151725.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Irwan
Solution Specialist
Solution Specialist

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.

Irwan_0-1719364212699.png

 

basically you need to find the previous value of INV then substract that to current value.

 

Result =
var _PreviousIndex = MAXX(FILTER('TBL-Data Raw','TBL-Data Raw'[Index]<EARLIER('TBL-Data Raw'[Index])&&'TBL-Data Raw'[P/N]=EARLIER('TBL-Data Raw'[P/N])),'TBL-Data Raw'[Index]) //find previous index with same date and same P/N
var _ValueCalc = MAXX(FILTER('TBL-Data Raw','TBL-Data Raw'[Index]=_PreviousIndex),'TBL-Data Raw'[INV]) //find value of INV based on located index in _PreviousIndex
Return
IF(
    'TBL-Data Raw'[DATE]=MIN('TBL-Data Raw'[DATE]), //conditional if to make Result zero for all first date
    0,
    'TBL-Data Raw'[INV]-_ValueCalc
)
 
_PreviousIndex is used for looking index for same P/N with previous date (seems direct MAXX to INV value will return into inaccurate INV since you have jump in date which is Saturday and Sunday).
_ValueCalc is for finding INV value based on index that was calculated in _PreviousIndex.
The conditional if is there to zero those first date value as you need.
 
Hope this will help you.
Thank you.

@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,


Irwan
Solution Specialist
Solution Specialist

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors