Nested IF Statement in a Filtered Dates Calculation

[CracktheCode85]

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! 

[AmiraBedh · Accepted solution]

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]

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???

[AmiraBedh · Accepted solution]

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]

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]

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

[CracktheCode85]

Hi @AmiraBedh 
Thank you for the assistance. The formula isn't picking up the 2 day look back for some reason. 

We are using a table that has "Day 01, Day 02, Day 03" etc.. to measure by, but no matter what formula I use I can't get the two day or even one day look back. 
So for the total Payments for Today -2, I still get the current total of payments... 

I'm wondering if it's the date tables configuration? 

[CracktheCode85]

I was exploring formulas with the logic in mind that I think a Running Total or the 'Payments' field in table 1 could be viewed up to 2 days prior from the current date. 

I think I just solved it! 

CALCULATE(

    SUM('Table'[Payments]),

    FILTER(

        ALLSELECTED('Dates'[Bank Day ]),

        ISAFTER('Dates'[Bank Day ], MAX('Dates'[Bank Day ]), DESC)

    )

)

This is essentially giving me the two day look back... but I'm verifying it now. 

[AmiraBedh · Accepted solution]

Don't forget to accept your solution !

[CracktheCode85]

The formula you provided is VERY VERY close to what is needed, but it doesn't correlate to previous months data and variates further each month.

Am I missing something? 

Cash Moving Avg =

VAR CurrentMaxDate = MAX('Dates'[Date])

VAR TargetDate = CALCULATE(

    MAX('Dates'[Date]),

    FILTER(

        'Dates',

        'Dates'[Bank Day Numbers] >= 1 && 'Dates'[Date] < CurrentMaxDate

    ),

    ALL('Dates'),

    TOPN(2, FILTER('Dates', 'Dates'[Bank Day Numbers] = 1 && 'Dates'[Date] < CurrentMaxDate), 'Dates'[Date], DESC)

)

RETURN

CALCULATE(

    SUM('Table[Payments]),

    FILTER(

        'Dates',

        'Dates'[Date] = TargetDate

    )

)