Forum Discussion
How can i compare different values? (which are grouped)
Measure 11 =
VAR GroupedTable = FILTER(SUMMARIZE(ALLSELECTED('Sales'),'Smart Date'[Date Look Up],"New Value",SUM('Sales'[Value])),[New Value])
RETURN
CALCULATE(sum([New Value]),FILTER(GroupedTable,PARALLELPERIOD('Smart Date'[Date Look Up],12,MONTH))) - CALCULATE(sum([New Value]),FILTER(GroupedTable,PARALLELPERIOD('Smart Date'[Date Look Up],0,MONTH)))Hi Guys just wondering why i cant reference the column i made in my variable, i am trying create a measure that basically computes the difference between the date values so i need to find a way of getting the groupedtable values for different dates.
The problem is when i try to reference the new value column i created which is the sum of values grouped by dates, it doesnt allow me to reference??
Can anyone figure out why or how i should approach this problem ?
Hi Anonymous,
You can try to use following measure formula to compare particular value (twelve month ago) and current value:
Measure= VAR GroupedTable = SUMMARIZE ( ALLSELECTED ( 'Sales' ), 'Smart Date'[Date Look Up], "New Value", SUM ( 'Sales'[Value] ) ) VAR currDate = MAX ( 'Smart Date'[Date Look Up] ) RETURN MAXX ( FILTER ( GroupedTable, [Date Look Up] = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 12, DAY ( currDate ) ) ), [New Value] ) - MAXX ( FILTER ( GroupedTable, [Date Look Up] = currDate ), [New Value] )If above not help, can you please share some sample data to help us clarify your data structure and requirement?
Regards,
Xiaoxin Sheng
7 Replies
Hi Anonymous,
You can try to use following measure formula to compare particular value (twelve month ago) and current value:
Measure= VAR GroupedTable = SUMMARIZE ( ALLSELECTED ( 'Sales' ), 'Smart Date'[Date Look Up], "New Value", SUM ( 'Sales'[Value] ) ) VAR currDate = MAX ( 'Smart Date'[Date Look Up] ) RETURN MAXX ( FILTER ( GroupedTable, [Date Look Up] = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 12, DAY ( currDate ) ) ), [New Value] ) - MAXX ( FILTER ( GroupedTable, [Date Look Up] = currDate ), [New Value] )If above not help, can you please share some sample data to help us clarify your data structure and requirement?
Regards,
Xiaoxin Sheng
AnonymousThank you so much your solution worked like a charm, do you mind briefly explaining the logic behind it so i can understand and be capable of reapplying it to different scenarios.
Anything brief would be appreicated if not regardless you have helped alot and have my gratitude.
Best wishes,
Kote
- AlB
Community Champion
Hi Anonymous
I'm not sure what you are trying to do but maybe try this:
SUMX(GroupedTable,[New Value])where you have this:
sum([New Value])
AlB Wouldnt that give me the sum of every date rather than sum per date ?
i mentioned above i wanted to calculate the difference between the values each date has, which is why i made the summarize table so i could group values by the date column.The idea is to have the value of Aug 16 - Aug 17 , which would give me the difference between the two months
- AlB
Anonymous
SUMX(Table1; Table[Col1])
is exactly the same as
SUM( Table[Col1])
In fact the latter gets converted into the former internally by the engine.
I was suggesting the alternative so that you can reference [New value]
