Forum Discussion

CracktheCode85's avatar
CracktheCode85
Helper II
2 years ago
Solved

Nested IF Statement in a Filtered Dates Calculation

Hello,

 

I'm using this formula to get the total sales for 2 business days ago.

So if today is Business day 16, this formula is showcasing the Total sales for business Day 14. 

However I want to be able to select different months and see the same data points for that specific month. 

But when I move months with my current calculation it shows the value as "Blank"

The calculation is:

Total Sales Minus 2 Days = CALCULATE(SUM('Table'[Payment]),
FILTER('Dates'
      ,'Dates'[Ref Day Numbers] >= 1
      && 'Dates'[Ref Day Numbers] <=CALCULATE(MAX('Dates'[Ref Day Numbers]), FILTER(Dates, Dates[Date] = TODAY()-2)
)))
 
 
The formula I was trying to use to accomplish for each month was:
Total Sales Minus 2 Days = CALCULATE(SUM('Table'[Payment]),
FILTER('Dates'
      ,'Dates'[Ref Day Numbers] >= 1
      && 'Dates'[Ref Day Numbers] <=CALCULATE(MAX('Dates'[Ref Day Numbers]), FILTER(Dates, Dates[Date] = IF('Dates'[Date] = TODAY(), TODAY() -2, 'Dates'[Date]))
)))
The second formula isn't catching the minus 2 days portion of the formula. 
Any help or guidance is greatly appreciated! 
 
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    Try the follwing : 

     

    Total Sales Minus 2 Business Days = 
VAR CurrentMaxDate = MAX('Dates'[Date])
VAR TargetDate = CALCULATE(
    MAX('Dates'[Date]),
    FILTER(
        'Dates',
        'Dates'[Business Day] = 1 && 'Dates'[Date] < CurrentMaxDate
    ),
    ALL('Dates'), 
    TOPN(2, FILTER('Dates', 'Dates'[Business Day] = 1 && 'Dates'[Date] < CurrentMaxDate), 'Dates'[Date], DESC)
)
RETURN
CALCULATE(
    SUM('Table'[Payment]),
    FILTER(
        'Dates',
        'Dates'[Date] = TargetDate
    )
)

8 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    Try the follwing : 

     

    Total Sales Minus 2 Business Days = 
VAR CurrentMaxDate = MAX('Dates'[Date])
VAR TargetDate = CALCULATE(
    MAX('Dates'[Date]),
    FILTER(
        'Dates',
        'Dates'[Business Day] = 1 && 'Dates'[Date] < CurrentMaxDate
    ),
    ALL('Dates'), 
    TOPN(2, FILTER('Dates', 'Dates'[Business Day] = 1 && 'Dates'[Date] < CurrentMaxDate), 'Dates'[Date], DESC)
)
RETURN
CALCULATE(
    SUM('Table'[Payment]),
    FILTER(
        'Dates',
        'Dates'[Date] = TargetDate
    )
)
    • CracktheCode85's avatar
      CracktheCode85
      Helper II
      2 years ago

      I'll have to play around with it some. The formula returned a "Blank" value for the card visual so I'm thinking I have the wrong fields in the wrong places. 
      I'll keep working with this and let you know! 

      • AmiraBedh's avatar
        AmiraBedh
        Super User
        2 years ago

        When done, please tag me so I cannot lose your thread 🙂

  • CracktheCode85's avatar
    CracktheCode85
    Helper II
    2 years ago

    Hey AmiraBedh 
    I've been trying to get this formula to work and it seems like BI is just ignoring the '-2' section of the formula??? 

     

    Two Day Lookback = CALCULATE(SUM('Table'[Payments]),

    FILTER('Dates'

          ,'Dates'[Bank Day Numbers] >= 1

          && 'Dates'[Bank Day Numbers] <=CALCULATE(MAX('Dates'[Bank Day Numbers]), FILTER(Dates, Dates[Date] = TODAY()-2)

    )))

    Am I not connecting the right tables to the 'Today()-2))' portion???