Using Max Date of Separate Table in Column Formula

[ozne99]

Hi all,

I have a budget dataset with one date column, which I am trying to filter.

I have two separate unjoined tables for Date to used to calculate two separate YTD amounts in the budget dataset, so I can compare YTD1 vs YTD2 based on the Date1 and Date2 slicers. I did not join the tables because I did not want the dates to affect the YTD calculations. That works fine, but where I am stuck is that I want to have a column to identify when the Budget Date is between the selected Date1 and Date2. I tried to use the following formula in a column:

IF(AND(BUDGETDATE <= MAX(DATE1), BUDGETDATE >= MAX(DATE2), 0,1)

The problem is that the MAX function takes into account the latest date in each of the Date tables, instead of what is selected in the slicer, so it always comes up as 1.

I feel like I might be overcomplicating this issue, but have been stuck on this for awhile. Any assistance is much appreciated!

Thanks in advance!

[ozne99 · Accepted solution]

Figured it out. Created a SUMX formula to force the calculations to occur at the row level and applied a filter to remove 0 from the visual

Formula: SUMX(BUDGET, IF(AND(BUDGETDATE <= MAX(DATE1), BUDGET DATE >= MAX(DATE2)),1,0)

[lbendlin]

"what is selected in the slicer"  - this immediately disqualifies the request as you cannot create columns from measures.

Have a look at the FILTERS() or VALUES() functions if you want to do this with measures.

[ozne99]

Thanks the tip; I've tried to use various combinations of VALUES() and FILTERS() early on, but not sure how to incorporate it into my need.

Ultimately, I want the column to flag dates less than the slicer selection, but not sure how to build that out.