Calculate/Display % sales difference this weeks sales on this week last year.

[Anonymous]

Hi All

I have a table showing volume sales by date. What i want to do is to provide a table and graph showing the % difference of the [Pallets] for this week vs this week last year. I figured i need a measure that is the % difference of the [Pallets] for this date vs this date last year. The key fields are;

Pallet sales = [Pallets] - Integer

Order date = [Order Date] - Date

I tried the below measure to calculate % difference date on date, but this is not working right.

divide(calculate(sum(qryHistoryStats[Pallets]),SAMEPERIODLASTYEAR(qryHistoryStats[Order Date].[Date])) - sum([Pallets]),calculate(sum(qryHistoryStats[Pallets]),SAMEPERIODLASTYEAR(qryHistoryStats[Order Date].[Date])))

Is my approach correct (in which case what is wrong with my measure) or is there another way to achieve this?

[v-lili6-msft]

hi, @Anonymous

You may try to add a weeknum column and drag it into visual

Weeknum = WEEKNUM(qryHistoryStats[Order Date]) 

Then use your formula

difference% = 
DIVIDE (
    SUM ( [Pallets] ) // this weeks sales
    - CALCULATE (
        SUM ( qryHistoryStats[Pallets] ),
        SAMEPERIODLASTYEAR ( qryHistoryStats[Order Date].[Date] )
    ) // difference between this weeks sales on this week last year
        ,
    CALCULATE (
        SUM ( qryHistoryStats[Pallets] ),
        SAMEPERIODLASTYEAR ( qryHistoryStats[Order Date].[Date] )
    ) // on this week last year
)

Result:

Best Regards,

Lin

[Anonymous]

Lin, thanks.

Week number is already a field in the data. Below is a sample of the data.

I used your formula but the measure returns zero on every row - PcntDiff on below snapshot table

I broke the formula down to see the the results of the 2 parts;

• SUM ( qryHistoryStats[Pallets] ) works OK - This Week on the above
• SAMEPERIODLASTYEAR ( qryHistoryStats[Order Date].[Date] ) returns the same value as the current year - Last Year Wk on the above

So the full formula essentially calculates as (x-x) / x

Why would SAMEPERIODLASTYEAR not return the correct Data?

[v-lili6-msft]

hi, @Anonymous 

Sorry for the late reply, SAMEPERIODLASTYEAR ( qryHistoryStats[Order Date].[Date] ) is used date hierarchy of qryHistoryStats[Order Date].

And from your screenshot, It seems that you just use year field, 

You should use [Order Date] field in the visual like this:

If it is not your case, please share pbix file or some data sample and expected output. Do mask sensitive data before uploading.

Best Regards,

Lin

[Greg_Deckler]

Time Intelligence functions can be tricky. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...