cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: How can i compare different values? (which are grouped)

Hi @Kote101,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
7 REPLIES 7
Super User

## Re: How can i compare different values? (which are grouped)

Hi @Kote101

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])

Regular Visitor

## Re: How can i compare different values? (which are grouped)

@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

Super User

## Re: How can i compare different values? (which are grouped)

@Kote101

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]

Regular Visitor

## Re: How can i compare different values? (which are grouped)

@AlB i tried, but it ends up giving me the total values of all the months combined when i try to use the measure in a table split by the months column, because of this i can't choose a particular month and use its value in calculations.

Super User

## Re: How can i compare different values? (which are grouped)

@Kote101

Your initial question referred to the inability to reference [New Value]. I understand that has been solved and you are asking something else now?

Community Support Team

## Re: How can i compare different values? (which are grouped)

Hi @Kote101,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Regular Visitor

## Re: How can i compare different values? (which are grouped)

@v-shex-msftThank 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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 243 members 2,674 guests
Recent signins: