Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago

Daily Percentage Change

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...
Need Help
Show and Tell
Tutorial Requests
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

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's avatar
    ElliotP
    Post Prodigy
    9 years ago

    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?