Daily Percentage Change

[ElliotP]

Afternoon,

I'm trying to calculate the percentage change in sales between days. So for example between wednesday the 23rd and tuesday the day before the 22nd.

At the moment I'm using a number of calculated columns which does the trick but I'm unable to only show the week's I would like to (the past week and past two weeks); I have my variables and filters which I use to achieve my showing only last week or two weeks ago through this for example;

P0WNetSales = 
VAR CurrentYear =
MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
CALCULATE (
MAX ( 'ExtendedCalendar'[WeekNum] ) - 0, FILTER(ALL( 'ExtendedCalendar' ), 'ExtendedCalendar'[Year] = CurrentYear )
)
RETURN

CALCULATE (
SUM('itemdetailsdogfood$'[Net Sales]),

'ExtendedCalendar'[Year] = CurrentYear,
'ExtendedCalendar'[WeekNum]
= CurrentWeekNum
)

Here is the code for my calculated columns spread between my date and data table;

ExtendedCalendar Table:

Datekey_Netsales = IF(CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]),ALLEXCEPT('itemdetailsdogfood$',ExtendedCalendar[DateKey]))=BLANK(),0,CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]),ALLEXCEPT('itemdetailsdogfood$',ExtendedCalendar[DateKey])))

Prev_Datekey = ExtendedCalendar[DateKey]-1

Prev_Netsales = IF(LOOKUPVALUE(ExtendedCalendar[Datekey_Netsales],ExtendedCalendar[DateKey],ExtendedCalendar[Prev_Datekey])=BLANK(),0,LOOKUPVALUE(ExtendedCalendar[Datekey_Netsales],ExtendedCalendar[DateKey],ExtendedCalendar[Prev_Datekey]))

Change$ = ExtendedCalendar[Datekey_Netsales]-ExtendedCalendar[Prev_Netsales]

$Change_% = DIVIDE((ExtendedCalendar[Datekey_Netsales]-ExtendedCalendar[Prev_Netsales]),ExtendedCalendar[Prev_Netsales])

itemdetailsdogfood$ Table:
 
 Netsales_Change = LOOKUPVALUE(ExtendedCalendar[Change$],ExtendedCalendar[Prev_Datekey],'itemdetailsdogfood$'[prev_date])
 
 Netsales_Change% = LOOKUPVALUE(ExtendedCalendar[$Change_%],ExtendedCalendar[Prev_Datekey],'itemdetailsdogfood$'[prev_date])
 Change% = DIVIDE(CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]), FILTER('itemdetailsdogfood$','itemdetailsdogfood$'[Date]=MAX('transactionsdogfood$'[Date]))), CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]), FILTER('itemdetailsdogfood$','itemdetailsdogfood$'[Date]=MIN('itemdetailsdogfood$'[Date]))),0)-1 

prev_date = 'itemdetailsdogfood$'[Date]-1

Admittedly it is the complicated way of achieving it, but none the less it's where I'm at, at the moment.

I've tried googling and having a look around the forum, but there doesn't seem to be an easy way for me to use my week variables and filter as to only show the week's I would like to (they dynamically change, so for example -1 shows last week).I've tried using some time intelligence functions but it doesn't seem to want to jam.

Link to my pbix: https://1drv.ms/u/s!At8Q-ZbRnAj8hkXYcgJYKWt8g0Ki

Any thoughts or help would be greatly appreciated.

[v-sihou-msft]

@ElliotP

In DAX, when doing "Previous X date part" calculation, if your scenario can't be achieved by general time intelligence function, you should use calculated column since the time intelligence need to be done on row level.

The expression can be more or less like:

Previous Week Sales =
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        Calendar,
        Calendar[Year] = EARLIER ( Calendar[Year] )
            && Calendar[WeekNumber]
                = EARLIER ( Calendar[WeekNumber] ) - 1
    )
)

However, it's not possible to dynamically select the "Previous X" and make it as filter for this kind of calculation. We have to create one calculated column for each calculation.

I have seen an approach to make a time period slicer for calculation, but it will expand the date table into several times bigger which is not a good practice, and it can be completely "dynamic" as you expected. So I don't think there's a better way than what you have done.

Regards,

[ElliotP]

That link is amazing and something I will definitly save for later as it could be incredibly useful for small projects to allow different dynamic time periods.

I really like my current filtering method but I really do only want to show the last 2 weeks for example of data. Aside from us being able to find a solution with the column idea (I feel has real potential, just needs a little more thought and time), I feel the best might be create a new query which just dynamically fitlers only the past 2 week (needs to be the past two weeks as opposed to just past 14days).

In terms of the column idea; I tried adapting it and working it into my current setup and I feel it has potential to work and be a viable option.

I did:

Previous Week Sales = 
CALCULATE (
    SUM ( 'itemdetailsdogfood$'[Net Sales] ),
    FILTER (
        ExtendedCalendar,
        ExtendedCalendar[Year] = MAX ( ExtendedCalendar[Year] )
            && ExtendedCalendar[WeekNum]
                = MAX ( ExtendedCalendar[WeekNum] ) - 3
    )
)

As well as the EARLIER versions in both my tables (DATE and DATA tables) as to try and ascertain something yet I wasn't able to show any values despite values being present and available. So I'm a little stumped.

This would be good to add in to my date table (works there seemingly) calculated columns:

Datekey_Netsales = 

IF(CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]),ALLEXCEPT('itemdetailsdogfood$',ExtendedCalendar[DateKey]))=BLANK(),0,CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]),ALLEXCEPT('itemdetailsdogfood$',ExtendedCalendar[DateKey])))

and

Prev_Netsales = 


IF(LOOKUPVALUE(ExtendedCalendar[Datekey_Netsales],ExtendedCalendar[DateKey],ExtendedCalendar[Prev_Datekey])=BLANK(),0,LOOKUPVALUE(ExtendedCalendar[Datekey_Netsales],ExtendedCalendar[DateKey],ExtendedCalendar[Prev_Datekey]))

Creating a dynamically filtered query might be the easiest option from here.

Any thoughts or ideas?