Forum Discussion

StephenK's avatar
StephenK
Resolver I
6 years ago
Solved

Adding Non-Contiguous Dates

Hello all,   I have a table like so:   Date Item QTY CurrentDayStock CurrentDayRestock CurrentDayTotalStock PrevDayStock PrevDayRestock NextDayStock NextDayRestock 4/3/2020 Widget...
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.
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.