cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@Visuals_PowerBI

Also make sure you are filtering  your page by the year column from your date table. It appears that it should by Sys_Year.

Data on Rows or Filters, as a rule of thumb, should come from your look up tables and not your fact table.

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Visuals_PowerBI Frequent Visitor
Frequent Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@kcantor

oh wow, it works now, perfect. I am still getting used to the working of Power BI.

I think the catch was > "Date on Rows or Filters - should come from  look up tables and not your fact table."

 

One last question -

Like we have the previous week sales from this year, how do I look at the Previous to Previous week sales same this year.

Example. Currently in 2017-w30, then Compare 2017-w29 verses 2017-w28.

So I already have one the measure(as below) calculated, how do I calculate the the other one.

Sales This Year  = CALCULATE([Total Sales], DATEADD(Calender[Date_dt], -7, Day))

 

Thank you again for your help, appreciate it.

Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year


You only need the one other calculation you listed. ThT is the best thing about powerbi, write once and re use. The measure you just shared is for sales last week. Put total sales on and you are set. For the variance you do week over week variance =[total sales]-[last week total sales].


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Visuals_PowerBI Frequent Visitor
Frequent Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@kcantor

Now I got both my visuals working - 

1. Sales for previous week This Year verses Sales for same week Last Year and variance

2. Sales for previous two weeks and variance

 

Refer below image, so if I filter on 2017-w31 then I get first visual correct but second wrong, 

while if I filter on 2017-w30 then I get second visual correct while first wrong.

 

As we  are currently in 2017-w31

1. Compare 2017-w30 vs 2016-w30

2. Compare 2017-w30 vs 2017-w29

 

I tried changing the measure but I am not able to get what should I change so that both visuals can sync up.

I dont want to show complete table, I only want to show the relevant data as above stated.

Below measure used - 

 

Total Sales = sum(SalesTable[Sales])

Sales Last Year = CALCULATE([Total Sales], DATEADD(DATEADD(Calender[Date_dt], -7, Day), -1, YEAR))

Sales This Year = CALCULATE([Total Sales], DATEADD(Calender[Date_dt], -7, Day))

Variance Sales = [Total Sales] - SalesTable[Sales This Year]

Variance = SalesTable[Sales Last Year] - SalesTable[Sales This Year]

 

 

 

 

 

 

capture 7.JPG

 

Edit:Below shown are the numbers for sample sales numbers -

 

capture 8.JPG

Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year

Your measures could be cleaned up and might fix the issues.


Total Sales = sum(SalesTable[Sales])

Sales Last Year = CALCULATE([Total Sales], DATEADD((Calender[Date_dt] -1, YEAR))

Sales This Year last week = CALCULATE([Total Sales], DATEADD(Calender[Date_dt], -7, Day))

Variance Sales year over year = [Total Sales] - [Sales Last Year]

Variance week over week = [total sales] - [Sales This Year last week]
Sorry if the capitalization is off. I am working on this from my phone.




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Visuals_PowerBI Frequent Visitor
Frequent Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

I did update based on your suggestion but still same issue. 

Community Support Team
Community Support Team

Re: HELP please, Previous Week comparison for THIS year verses LAST year

Hi @Visuals_PowerBI,

 

I think we don't need time intelligence functions in your scenario. Maybe you could try this.

 

SameWeekLastYear =
VAR ContextYear =
    MIN ( 'SalesTable'[Sales_Year] )
VAR ContextWeek =
    MIN ( 'SalesTable'[Sales_Week] )
RETURN
    CALCULATE (
        SUM ( SalesTable[Sales] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Sales_Year]
                = ContextYear - 1
                && SalesTable[Sales_Week] = ContextWeek
        )
    )
YearVariance =
SUM ( SalesTable[Sales] ) - [SameWeekLastYear]
PreviousWeek =
VAR ContextDate =
    MIN ( SalesTable[Sales_WeekEndDate] )
RETURN
    CALCULATE (
        SUM ( SalesTable[Sales] ),
        FILTER ( ALL ( SalesTable ), 'SalesTable'[Sales_WeekEndDate] = ContextDate - 7 )
    )
WeekVariance =
SUM ( 'SalesTable'[Sales] ) - [PreviousWeek]

HELP please, Previous Week comparison for THIS year verses LAST year.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: HELP please, Previous Week comparison for THIS year verses LAST year

Hi @Visuals_PowerBI,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
viferenc Regular Visitor
Regular Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

 Dear Dale,

 

thank you for the help. It helps me also a lot.

 

I have still an issue with the total.

 

When I use the formula Sameweeklastyear the total sum of this line is not correct

Sameweeklastyear.jpg

 

 

 

 

 

The correct sum of the least year week should be 46.796.334 but I have 9.554.492

 

Could you please give me an advise how should I can get the correct sum.

 

Thank you for your help.

 

Br

 

Feri