Forum Discussion

CracktheCode85's avatar
CracktheCode85
Icon for Helper II rankHelper 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 ...
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
    )
)
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    Don't forget to accept your solution !

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper 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
Icon for Helper II rankHelper 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
    Icon for Super User rankSuper User
    2 years ago

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

    • CracktheCode85's avatar
      CracktheCode85
      Icon for Helper II rankHelper II
      2 years ago

      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's avatar
        CracktheCode85
        Icon for Helper II rankHelper II
        2 years ago

        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.