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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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