Forum Discussion

StephenK's avatar
StephenK
Resolver I
6 years ago
Solved

Adding Non-Contiguous Dates

Hello all,

 

I have a table like so:

 

DateItemQTYCurrentDayStockCurrentDayRestockCurrentDayTotalStockPrevDayStockPrevDayRestockNextDayStockNextDayRestock
4/3/2020Widgetea63006306300  
4/6/2020Widgetea630152782 00 

 

The PrevDay and NextDay columns are calculated columns that pull the previous/next day inventory using a variant of the following DAX measure:

 

NextDayStock =
VAR __Date = 'Fact'[Date]
VAR __Item = 'Fact'[Item]
VAR __Facility = 'Fact'[Facility]
VAR __Qty = 'Fact'[Qty]
VAR __Restock = 'Fact'[CurrentDayRestock]
VAR __Original = 'Fact'[CurrentDayStock]
VAR __Next =
MAXX(
FILTER(
'Fact',
[Date] = (__Date + 1) *1. &&
[Item] = __Item &&
[Facility] = __Facility &&
[Qty] = __Qty
),
[CurrentDayStock]
)
RETURN __Next

The idea is that I am pulling the NextDay/PrevDay stock into each row so that every row has the current stock, next day stock and previous day stock. All to get daily use and estimated days stock remaining.

I realized that there is an issue with this formula when the weekend hits. On a Friday, the row attempts to pull the next day stock, but it's looking for Saturday, which doesn't exist in the dataset. I need a way to account for this in the formula. Weekends should be excluded, so Friday pulls NextDayStock from Monday and vice versa. Anyone have any ideas on how I could accomplish this in my formula? 

 
Date
Date and Time
Date measures to filter data.
DAX
dax measure
  • StephenK's avatar
    StephenK
    6 years ago

    I think I figured it out: 

    VAR __Next_Date = CALCULATE(MIN('Fact'[Date]),FILTER('Fact','Fact'[Date]>__Date))

    VAR __Next =
    MAXX(
    FILTER(
    'Fact',
    [Date] = __Next_Date &&
    [Item] = __Item &&
    [Facility] = __Facility &&
    [Qty] = __Qty
    ),
    [CurrentDayStock]
    )

    Since my fact table has non contiguous dates already excluding weekends, I realized I could just write an additional variable that returns the Min date after the current date or Max date before the current date to get my NextDate/PrevDate. Then I just plugged the variable into my formula to replace the (__Date + 1) piece.

2 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    HI StephenK ,

     

    I actually don't understand your DAX expression part where you have - [Date] = (__Date + 1) *1. &&

    Check following article on how you can exclude weekends from your calculation and try to incorporate similar logic in your dax:

    https://www.powerquery.training/networkdays/

    https://stackoverflow.com/questions/40335558/exclude-weekends-in-powerbi-report

     

    If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

     

    Thanks,

    Pragati

    • StephenK's avatar
      StephenK
      Resolver I
      6 years ago

      I think I figured it out: 

      VAR __Next_Date = CALCULATE(MIN('Fact'[Date]),FILTER('Fact','Fact'[Date]>__Date))

      VAR __Next =
      MAXX(
      FILTER(
      'Fact',
      [Date] = __Next_Date &&
      [Item] = __Item &&
      [Facility] = __Facility &&
      [Qty] = __Qty
      ),
      [CurrentDayStock]
      )

      Since my fact table has non contiguous dates already excluding weekends, I realized I could just write an additional variable that returns the Min date after the current date or Max date before the current date to get my NextDate/PrevDate. Then I just plugged the variable into my formula to replace the (__Date + 1) piece.