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

[rwverhovec]

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:


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).  

[Irwan · Accepted 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.

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.

[Ashish_Mathur · Accepted solution]

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.

[Irwan · Accepted 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.

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.

[rwverhovec]

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

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.