Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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(Grou...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

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]

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

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