Forum Discussion
Trend Visualization compared to previous week
Hi,
I try to visualize a change in a number (a sum of trucks per week) compared to the previous calender week.
For example...in Calendar Week 15 I counted 380 and in Calendar week 16 I counted 368. I now want to have a visual where I see a trend based on the previous week. In this case a negativ trend as the number went down.
Problem for me is to define something which is only looking at the previous CW.
Yes I´m working with a date table and already sorted out how to add a WeekNum in there.
Please note that I´m quite new to PBI.
Thanks for your help.
The myMax variable is essentially grabbing the current weeknum in the context of your visual. So, myMax - 1 essentially dials this back a week. So, if your current week is 16, when you are filtering for last week you want the rows where WeekNum is 15.
7 Replies
Can you post some mock data in text that can be easily copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
In general however, you should be able to create a measure that does a MAX of your weeknum and then you would use FILTER(ALL(),[weeknum]=yourmax) like:
Measure = VAR myMax = MAX('Table'[WeekNum]) VAR myCount = SUM('Table'[Trucks]) VAR tmpTable = FILTER(ALL('Table'),[WeekNum]=myMax-1) VAR lastWeekCount = COUNTX(tmpTable,[Trucks]) RETURN myCount - lastWeekCountTotally guessing here because no mock data.
Hi Greg,
Sorry I read your post too late.
I was able to adjust the formula given above
Trend Truck = VAR myMax = MAX('Date'[WeekNum]) VAR myCount = SUM('Table1'[Anzahl Trucks]) VAR tmpTable = FILTER(ALL('Date');[WeekNum]=myMax-1) VAR lastWeekCount = COUNTX(tmpTable;[Anzahl Trucks]) RETURN myCount - lastWeekCountI think I understand what you did there but what does "myMax-1" do?
What Visual would you use to display a trend "this week compared to last week"?
Thanks for your help so far!
The myMax variable is essentially grabbing the current weeknum in the context of your visual. So, myMax - 1 essentially dials this back a week. So, if your current week is 16, when you are filtering for last week you want the rows where WeekNum is 15.
