cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EalTim
Helper I
Helper I

Return Movement ON and AFTER the Base Month

I have a Table as shown below:

EalTim_0-1631694364608.png

From the table I created a table visual with Product slicer.

EalTim_1-1631692769977.png

The movement is the difference in Average of Cost and Average of Base Price.

The requirement is that whenever I select product in a slicer, it should display the movement on and after the base Month. For example, if i select product A from the slicer, the table visual will display 

EalTim_2-1631693219758.png

and if I select product B, then it will display

EalTim_3-1631693340893.png

and so on when other products are selected. 

Hence, if no product is selected then it show default to it normal table visual where all movement displays.

How can i achieve this?

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @EalTim ,

 

 

Please try the following measure:

 

Movement = 
CALCULATE (
    AVERAGE ( 'Table'[Cost] ) - AVERAGE ( 'Table'[Base Price] ),
    FILTER ( 'Table', 'Table'[Month] >= 'Table'[Base Month] )
)

vkkfmsft_0-1631868301078.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@EalTim , Create measure like

 

[Avg of Cost] - calculate([Avg of Cost] , filter(allselected(Table[Month]), Table[Month] <= Max(Table[Month])))

 

or

 

[Avg of Cost] - calculate([Avg of Cost] , filter(allselected(Table), Table[Month] <= Max(Table[Month])))

 

[Avg of Cost] - calculate(averageX(Table[Month],[Avg of Cost])  , filter(allselected(Table), Table[Month] <= Max(Table[Month])))

 

Assume [Avg of Cost]  is a measure



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi amitchandak, 

Thanks for the dax. I have rewritten your dax to work.

[Avg of Cost] = CALCULATE([Avg of Cost], FILTER(ALLSELECTED(Table), Table[Month] >= MAX(Table[BaseMonth])))

This works fine. However, when no Product or all Products is selected on the slicer, then it should display all movement. for example as shown below when no product is selected

EalTim_0-1631697069887.png

 

v-kkf-msft
Community Support
Community Support

Hi @EalTim ,

 

 

Please try the following measure:

 

Movement = 
CALCULATE (
    AVERAGE ( 'Table'[Cost] ) - AVERAGE ( 'Table'[Base Price] ),
    FILTER ( 'Table', 'Table'[Month] >= 'Table'[Base Month] )
)

vkkfmsft_0-1631868301078.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Hi amitchandak, the dax is not working. I don't know if it is because it doesn't have baseMonth in the dax.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!