Forum Discussion

TimonMeyer's avatar
TimonMeyer
Frequent Visitor
8 years ago
Solved

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 counte...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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 - lastWeekCount

Totally guessing here because no mock data.

  • TimonMeyer's avatar
    TimonMeyer
    Frequent Visitor
    8 years ago

    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 - lastWeekCount

    I 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!

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      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.

      • TimonMeyer's avatar
        TimonMeyer
        Frequent Visitor
        8 years ago

        Thanks! I somehow managed to get the data right.

         

        THX!