Comparison of Rolling 6 Month Values to Filter Change or No Change in Values for all 6 Months

brianarwiley — Mon, 27 Feb 2023 16:34:40 GMT

I am attempting to compare inventory values at the end of the month for a rolling 6 months. The table I am using records inventory on the last day of the month as period/year, which is converted to a date. I have added measures for each month for six months, and a calculated column for change or no change so the values can be filtered. The issue I am running in to is: the items that show no change are $0 value only, and I assume that is because the calculated column doesn't stop at 6 months, but continues indefinitely.

The first measure I tried for calculating the sum for the rolling 6 months was:
M1 = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),FILTER(ALLSELECTED('FIN IC_PART_PERIOD_END_STATUS_LOT'[Date]),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] = DATE(YEAR(TODAY()),MONTH (TODAY())-1,1)),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])
I made six of these, and they seemed to work for calculating the values for the months.

The first calculated column I created was:
Change Over Time =

VAR CheckOne = CALCULATE([M1]-[M2])

VAR CheckTwo = CALCULATE([M2]-[M3])

VAR CheckThree = CALCULATE([M3]-[M4])

VAR CheckFour = CALCULATE([M4]-[M5])

VAR CheckFive = CALCULATE([M5]-[M6])

VAR CheckSix = CALCULATE(CheckOne + CheckTwo + CheckThree + CheckFour + CheckFive)

VAR Change = IF(CheckSix = 0, "No Change", "Change")

RETURN Change
When put into a slicer, it only showed "No Change" and nothing filtered when selected.

The next calculated column I tried was:
Change Over Time =

VAR CheckOne = CALCULATE([M1]-[M2])

VAR CheckTwo = IF(CheckOne = 0,CALCULATE([M2]-[M3]), 1)

VAR CheckThree = IF((CheckOne + CheckTwo) = 0, CALCULATE([M3]-[M4]), 1)

VAR CheckFour = IF((CheckTwo + CheckThree) = 0, CALCULATE([M4]-[M5]), 1)

VAR CheckFive = IF((CheckThree + CheckFour) = 0, CALCULATE([M5]-[M6]), 1)

VAR CheckSix = IF(CheckFive = 0, "No Change", "Change")

RETURN CheckSix
This showed "Change" and "No Change" in the slicer, but when "No Change" was selected, it only showed items at $0, ignoring everything over.

I also tried using this measure:
M1 = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),DATEADD('FIN IC_PART_PERIOD_END_STATUS_LOT'[Date],-1,MONTH),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])
but it gave me a circular reference error when I created more than one.

I also tried this calculated column:
Change Over Time =

VAR CurrentYearMonth = DATE(YEAR(TODAY()),MONTH(TODAY()),1)

VAR FirstDifference = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),FILTER('FIN IC_PART_PERIOD_END_STATUS_LOT','FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] <= CurrentYearMonth && 'FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] >= CurrentYearMonth - 1),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])

VAR SecondDifference = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),FILTER('FIN IC_PART_PERIOD_END_STATUS_LOT','FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] <= CurrentYearMonth - 1 && 'FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] >= CurrentYearMonth - 2),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])

VAR ThirdDifference = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),FILTER('FIN IC_PART_PERIOD_END_STATUS_LOT','FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] <= CurrentYearMonth - 2 && 'FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] >= CurrentYearMonth - 3),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])

VAR FourthDifference = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),FILTER('FIN IC_PART_PERIOD_END_STATUS_LOT','FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] <= CurrentYearMonth - 3 && 'FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] >= CurrentYearMonth - 4),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])

VAR FifthDifference = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),FILTER('FIN IC_PART_PERIOD_END_STATUS_LOT','FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] <= CurrentYearMonth - 4 && 'FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] >= CurrentYearMonth - 5),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])

VAR SixthDifference = CALCULATE(SUM('FIN IC_PART_PERIOD_END_STATUS_LOT'[Extended Cost]),FILTER('FIN IC_PART_PERIOD_END_STATUS_LOT','FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] <= CurrentYearMonth - 5 && 'FIN IC_PART_PERIOD_END_STATUS_LOT'[Date] >= CurrentYearMonth - 6),'FIN IC_PART_PERIOD_END_STATUS_LOT'[Part Code])

VAR CheckOne = CALCULATE(FirstDifference - SecondDifference)

VAR CheckTwo = IF(CheckOne = 0, CALCULATE(SecondDifference - ThirdDifference), 1)

VAR CheckThree = IF((CheckOne + CheckTwo) = 0, CALCULATE(ThirdDifference - FourthDifference), 1)

VAR CheckFour = IF((CheckTwo + CheckThree) = 0, CALCULATE(FourthDifference - FifthDifference), 1)

VAR CheckFive = IF((CheckThree + CheckFour) = 0, CALCULATE(FifthDifference - SixthDifference), 1)

VAR CheckSix = IF(CheckFive = 0, "No Change", "Change")

RETURN CheckSix
but it also gave me a circular reference error.

topic Comparison of Rolling 6 Month Values to Filter Change or No Change in Values for all 6 Months in DAX Commands and Tips

Comparison of Rolling 6 Month Values to Filter Change or No Change in Values for all 6 Months