Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
In a page , I’m having a date range between slicer and showing card e.g. total sales which is simple sum aggregation. scenario needed : I want to create a %change card with indicator 🔺(green)🔻(red) which is possible BUT the problem statement is :- Example: When ever a user select any random date range slicer lets say 1Oct ,21- 10Oct,21 then the previous value should calculate from 22Sep,21 - 1Oct,21 or let’s say if user selects 20 Oct - 25 Oct then the previous value should calculate from 15 Oct - 20 Oct so, just taking the previous for the same date range difference whatever user selects.
How to calculate the previous value in Dax for the % change calculation?
% change formula = (present- previous/ previous ) *100
Thank in advance
Solved! Go to Solution.
Hi @SUMESHKUMAR22 ,
I have built a data sample
Please try the following formula to create a measure:
% change formula =
var _curr=CALCULATE(SUM('Table'[Values]),DATESBETWEEN('Table'[Date],MIN('Table'[Date]),MAX('Table'[Date])))
var _diff=DATEDIFF(MIN('Table'[Date]),MAX('Table'[Date]),DAY)
var _pre=CALCULATE(SUM('Table'[Values]), DATESBETWEEN('Table'[Date],MIN('Table'[Date])-_diff,MIN('Table'[Date])))
return (_curr - _pre)/_pre
Then format the measure:
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share some data to work with.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @SUMESHKUMAR22 ,
I have built a data sample
Please try the following formula to create a measure:
% change formula =
var _curr=CALCULATE(SUM('Table'[Values]),DATESBETWEEN('Table'[Date],MIN('Table'[Date]),MAX('Table'[Date])))
var _diff=DATEDIFF(MIN('Table'[Date]),MAX('Table'[Date]),DAY)
var _pre=CALCULATE(SUM('Table'[Values]), DATESBETWEEN('Table'[Date],MIN('Table'[Date])-_diff,MIN('Table'[Date])))
return (_curr - _pre)/_pre
Then format the measure:
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi ,
@v-eqin-msft , thank you 🙂
Also, wanted to check that if I create one measure for _curr & another measure for % change _pre. & Now if i use the Sum of value as a direct reference inside the calculate function for % change _pre then the % change logic will give incorrect answer. ( becuase the explicit date filter is applied in the report).
So, how can we override that filter condition thats is automatically getting applying in that reference measure ( that is _curr)?
I tried to use all function but its not working. Also I have one fact table and the date is mapped with the date dimension ( one- many ).
HI @v-eqin-msft ,
In addition to your above dataset if we take another Column that is Category (A,B,C,D) then what changes to be done in the measure to find the % change according to the Category also (which will be a filter along with date ).
Because there might be a situation where the %change will be 0 for the ones where one of the category date started in between so then in that case %change will be blank / 0 for the previous value calculation.
Please let us know how to update the measure for the same.
Thanks in advance
Hi,
Share some data to work with.
Hi everyone,
Please find below the dataset link.
Dummy Dataset
Solutions will be highly appreciated!
Thanks
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks for the solution!
You are welcome.
Hi @v-eqin-msft & All,
Please, let me know the updated Measure for the additional scenario thats is mentioned above.
Thanks in advance
What is the logic
when a user selecte same start and end date , how power bi need to understand to select a previous month date?!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |