Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SUMESHKUMAR22
Helper III
Helper III

Calculation the previous value for % change based on date slicer selection

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

3 ACCEPTED SOLUTIONS
v-eqin-msft
Community Support
Community Support

Hi @SUMESHKUMAR22 ,

 

I have built a data sample

Eyelyn9_0-1636510696241.png

 

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:

Eyelyn9_1-1636510751408.png

 

Output:

Eyelyn9_2-1636510775887.png

Eyelyn9_3-1636510817950.png

 

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.

View solution in original post

Hi,

Share some data to work with.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
v-eqin-msft
Community Support
Community Support

Hi @SUMESHKUMAR22 ,

 

I have built a data sample

Eyelyn9_0-1636510696241.png

 

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:

Eyelyn9_1-1636510751408.png

 

Output:

Eyelyn9_2-1636510775887.png

Eyelyn9_3-1636510817950.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for the solution!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @v-eqin-msft  & All,

Please, let me know the updated Measure  for the additional scenario thats is mentioned above.

Thanks in advance

Hi, 
Thanks! @v-eqin-msft for the solution . I also approched the same method 🙂

VijayP
Super User
Super User

@SUMESHKUMAR22 

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?!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.