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

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

    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