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

[Kote101]

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 ?

[v-shex-msft · Accepted solution]

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

[Kote101]

@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

[AlB]

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

[Kote101]

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

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

[Kote101]

@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.

[AlB]

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