DAX - filter based on measure results

sassaboss

New Member
Joined
Oct 8, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I wish to calculate percent of change from month to month and to automatically return values only with fulfilled conditions.
Can I do all that using just DAX.
My thinking is something like this.
I have May and April for example. And columns with Date, Product_Name and Product_Sales.
Tot_SUM := sum(Table[Product_Sales])
SUM_PrMonth := CALCULATE( [Tot_SUM] ; PREVIOUSMONTH( Table[Date] ))
Percent_Change := Tot_SUM / SUM_PrMonth - 1

And in slicer I will choose month. Now, I want to show in table in Power BI only rows with product names where Percent of change is less than -20% and greater than 20%.
I tried with a given formula but unfortunately not very successful.
Measure := CALCULATE( [Tot_SUM]; FILTER( Table; [Percent_Change] < 0,2 && [Percent_Change] > 0,2 ))

Thanks in advance :)
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,238
Well, you can do it easily using the filter pane on the right of the report canvas. Just drag the measure as a new filter (on the visual, or the page) and set the filter as you please.

if you want to use DAX, you need to fix your code

Measure := CALCULATE( [Tot_SUM]; FILTER( Table; [Percent_Change] < -0,2 || [Percent_Change] > 0,2 ))
 

sassaboss

New Member
Joined
Oct 8, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Well, you can do it easily using the filter pane on the right of the report canvas. Just drag the measure as a new filter (on the visual, or the page) and set the filter as you please.

if you want to use DAX, you need to fix your code

Measure := CALCULATE( [Tot_SUM]; FILTER( Table; [Percent_Change] < -0,2 || [Percent_Change] > 0,2 ))
Great :)
I just realised that I used && and had a lot of problems. Thanks.
I will try to do that will filter pane also.

Many thanks for the answer.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,771
Messages
5,542,433
Members
410,552
Latest member
Yogesh977
Top