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 ...
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 !

CracktheCode85's avatar
CracktheCode85
Helper 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
Helper 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. 
  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    Don't forget to accept your solution !

    • CracktheCode85's avatar
      CracktheCode85
      Helper II
      2 years ago

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