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?