Dax Measure to Calculate Dynamic percentage change

DAX DAX Calculations
1

Hi Team,

I have POS data with me.

I want to create a measure which will give me change in percentage of a specific column basis the week selected dynamically.

Now the catch here is, if the user selects any 4 weeks, I have to calculate the percentage change for the 4 weeks prior to it. If the user selects 5 weeks then i have to calculate the percentage change for 5 week prior to the selected 5 weeks.

EG: If I select week 21,22,23,24 then the change should be calculated taking into consideration 17,18,19,20 weeks. Lets say if its volume the filed would be set to sum of volume sales and percentage had to be calculated on that.

Pls guide me through this.

Thanks & Regards,
Hari G

2

Hi @HariGanesan.
Please provide a small sample dataset and Excel mock-up of your desired results (using that sample dataset) to assist the forum members in their investigations.
Greg

P.S.: What’s POS?

1 Like
3

Hi @HariGanesan,

Just following up on the request above.

Thanks

4

Hi Greg,

I have attached a sample excel file.

So here is what i am trying to achieve:

I will have a week slicer in my pbi file.

So i want the percentage change in volume to be calculated dynamically based on user selected weeks.

We know mathematically change in volume is (new-old)/old.

So when the user selects one week the volume change must be calculated based on the previous week to the selected week and if the user selects two weeks the change must be calculated based on the previous two weeks prior to the selected weeks.

Ex: If the user selects week 4 and 5, the old value should be taken as a aggregate of the sum of volume of week 2 and 3. Since the user has selected 2 weeks and the min of those two weeks selected is 4 from that we want to take two weeks prior from 4.

If the user selects three weeks like week 4,5,6 then for the old the weeks should be selected as 1,2,3.

I am hoping the team and community here can help me achieve this.

Sample.xlsx (9.0 KB)

Thanks in Advance,
Hari G

5

Hi Greg,

By POS I meant Point of Sales.

Thanks,
Hari G

6

Hi @HariGanesan ,

I propose the following idea- since multiple weeks can be selected

  • measure that based on min(week)

  • and count(number of weeks)

On top of your Sample (I change October 2rd date format) I add Date dimension

In sample idea pbix I add following measures to show idea:

  • Current Sales Volume
Sales Vol = Sum('Sample'[Volume Sales])
  • Previous weeks calculation ( based on minimum week and week counts)
Prev Weeks Calc =

VAR _WeekMin = CALCULATE(Min('Date'[ISO Weeknumber]) ,ALLSELECTED('Date'[Date]))

Var _WeekCount  = DISTINCTCOUNT('Date'[ISO Weeknumber])

VAR _Result =

    CALCULATE ([Sales Vol],

    FILTER (ALL('Date'),

      'Date'[ISO Weeknumber] <_WeekMin     &&   'Date'[ISO Weeknumber] >=_WeekMin-_WeekCount

    ) )

RETURN _Result
  • % change - just to follow formula (new-old)/old
% Change weeks = DIVIDE ([Sales Vol]-[Prev Weeks Calc],[Prev Weeks Calc],0)

So it can be displayed :

image
image1081×313 12.2 KB

Sample Idea Pbix:
Sample_Idea_for_Hari.pbix (67.8 KB)
Sample.xlsx (9.1 KB)

Hoping you find this useful and meets your requirements that you’ve been looking for.
Good luck with your POS data.

Best regards,
Maja

7

Hi @HariGanesan did the response provided by @mspanic help in solving your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark the answer that solved your query as the SOLUTION. Thanks!

8

Hello @HariGanesan Due to the length by which this post has been active, we are tagging it as Solved. Thanks to the contributors to this post. For further questions related to this post, please make a new thread. Feel free to reopen this thread if anyone would like to answer the pending inquiry above.