WEEK VARIATION

[Miguelcrz]

Hello,

I need to have the variation % of my sales comparing it vs the last week like this:

My power BI currently looks like this:

And my table of time looks like this (Semanacal = Number of the week):

I've also looked at the forum the questions of people that had the same issue but isn't working for me.

Thanks!

[amitchandak]

@Miguelcrz , first of all have a separate week table. Should include the year in that.  The best way is to use week year rank , Please follow my blog for that.

With week no. The date can Week -year/week table

This Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week]=max('Date'[Week])))
Last Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week]=max('Date'[Week])-1))

With Rank

This Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

refer these to see how rank works

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[Anonymous]

Hi @Miguelcrz,

You can try to use the following measure formula if it meets your requirement.

Measure =
VAR currWeek =
    MAX ( table[Week] )
VAR prevSales =
    CALCULATE (
        SUM ( table[Sales] ),
        FILTER ( ALLSELECTED ( table ), [Week] = currWeek - 1 )
    )
RETURN
    DIVIDE ( SUM ( table[Sales] ) - prevSales, prevSales, -1 )

If above not help, please provide some dummy data to help us clarify your scenario and do test coding formula on it.
Regards,

Xiaoxin Sheng

[FrankAT]

Hi @Miguelcrz 

is that what you are looking for?

Regards FrankAT

[Anand24]

Hi @Miguelcrz ,

Can you create a new column with below DAX:

Last Calendar Week Sales = CALCULATE( [Total Sales], 'Calendar'[Week Number] = WEEKNUM(TODAY(),1) -1 )

Use this to create another calculated column for % variation with below DAX:

Percent_Variation = (([Total Sales]-[Last Calendar Week Sales])/[Last Calendar Week Sales])*100

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anand24]

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Miguelcrz]

Hi @Anand24 @amitchandak @v-shex-msft @FrankAT ! is not working. I only have the column week and the column sales. I have to calculate or do a new column to calculate the sales from last week but i dont know how. Then i need to calculate the variation between the sales of this week vs last week.

Thanks!

[FrankAT]

Hi @Miguelcrz 

here is another solution to your question. Use the three measures below.

Regards FrankAT

[Miguelcrz]

HI @FrankAT , it doesnt work :(.

[Anonymous]

Hi @Miguelcrz,

You told us these codes not works without any detailed information, they not help to resolve your scenario and hard to clarify/troubleshoot the issues.
Can you please provide a pbix file with dummy data and formulas to test? I think it should help for testing the coding formula on your sample data. (remove sensitive data before share)

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Miguelcrz]

Hi! is not working. I only have the column week and the column sales. I have to calculate or do a new column to calculate the sales from last week but i dont know how. Then i need to calculate the variation between the sales of this week vs last week.

Thanks!

[FrankAT]

Hi @Miguelcrz

here another solution to your question. Use the three measures below.

Regards FrankAT