cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kote101 Regular Visitor
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
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
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])

 

 

Kote101 Regular Visitor
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
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]

 

Kote101 Regular Visitor
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.

Highlighted
Super User
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
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: | |
Kote101 Regular Visitor
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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 47 members 1,168 guests
Please welcome our newest community members: