Hello Community,
I am trying to calculate the running total of a value based on 2 different columns, date and type. I have a measure that does this successfully for 2 different values, however, now I need to be able to compare the those 2 running totals to find which RT of value 2 is the closest to value 1. The problem that I have encountered is that I cannot use the MAX function on a measure, so I have been trying to recreate my running total on a calculated column to achieve what I need.
The measures I have that work perfectly right now are:
RT Value1:= IF(SELECTEDVALUE(Table[DATE])
> calculate(
LASTNONBLANK(Table[DATE], 1),
FILTER(all(Table),Table[Value1] <> 0)),BLANK(),
CALCULATE(
SUM('Table'[Value1]),
FILTER( ALLSELECTED('Table'[DATE]), 'Table'[DATE] <= MAX(Table[DATE])
)
))
RT Value2:= CALCULATE(
SUM('Table'[Value2]),
FILTER(
ALLSELECTED('Table'[DATE]),
ISONORAFTER('Table'[DATE], MAX('Table'[DATE]), DESC)
)
)
This is the formula I am using for my Calculated Column that is not working:
RT Value1 Calc Column = IF(SELECTEDVALUE(Table[DATE])
> calculate(
LASTNONBLANK(Table[DATE], 1),
FILTER(all(Table),Table[Value1] <> 0)),BLANK(),
CALCULATE(SUM(Table[Value1]),FILTER(ALLEXCEPT(Table,Table[Type]), Table[DATE] <= MAX(Table[DATE]))))
Here is an example of my dataset:
| Date | Value1 | RT Value1 | RT Value1 Calc Column |
| 6/30/2020 | $0 | $0 | $5,216,939 |
| 7/31/2020 | $18,322 | $18,322 | $31,301,637 |
| 8/31/2020 | ($13,669) | $4,653 | $32,692,821 |
| 9/30/2020 | $45,317 | $49,970 | $50,082,619 |
| 10/31/2020 | $49,553 | $99,523 | $49,734,823 |
| 11/30/2020 | $27,554 | $127,077 | $43,474,495 |
| 12/31/2020 | $120,843 | $247,920 | $44,170,087 |
| 1/31/2021 | $27,568 | $275,488 | $49,387,027 |
| 2/28/2021 | $33,303 | $308,791 | $47,648,047 |
| 3/31/2021 | $39,005 | $347,796 | $52,864,986 |
| 4/30/2021 | $0 | $347,796 | $21,563,350 |
| 5/31/2021 | $0 | | $21,911,146 |
| 6/30/2021 | $0 | | $21,911,146 |
| 7/31/2021 | $0 | | $21,911,146 |
| 8/31/2021 | $0 | | $24,345,717 |
Type in my dataset is the item type for each of of those values and dates, meaning, I need the running total calculated for each item based on the dates; as I change my filter from item 1 to item 2, etc, the new running total that comes up will be for that new item selected.
I have been trting different formulas based on Community posts regarding running totals for columns, but nothing has worked so far and I believe I have exhausted my search.
Thank you!
