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.
