Forum Discussion

cham's avatar
cham
Post Patron
7 years ago

Difference between previous week data

Hi ,   I used following measures to calculate difference between previous week data,   table = Calendar (dateStart;dateEnd)   Measure = calculate(sum('Sheet1'[Total]), filter('Table', 'Table'...
General Comment
Show and Tell
Tutorial Requests
cham's avatar
cham
Post Patron
7 years ago

Hi Anonymous 

 

Yes I alredy create a new calender in my sheet and i make a relationship between calender table and my sheet date. I want to know what is wrong with my measures which i show in earlier. Can you please explain it to me?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

cham - Here are some issues:

1. The visual you show appears to use the date from Sheet1. You will want to use the date from the date table in any visual.

2. Consider the following measure:

 

Measure = calculate(
    sum('Sheet1'[Total]),
    filter(
        'Table', 
       'Table'[Column] = WEEKNUM(max('Sheet1'[Date]),1)
    )
)

I think you are trying to find the week number of the max date in Sheet1.

 

1.a. One issue is that WEEKNUM returns a number, but it is being used to filter a date column (I think).

1.b. Another problem is WEEKNUM function will not work when crossing over years.

The measure instead could be like this:

 

Measure = 
--var max_date = CALCULATE(max('Sheet1'[Date]), ALL('Date')) --If you need to only find the most current date in the fact table, you can use these 2 lines.
--var week_num = CALCULATE(MAX('Date'[WeekNumCumulative]), 'Date'[Date] = max_date)
var week_num = MAX('Date'[WeekNumCumulative]) --Find the maximum week number associated with the relevant date.
return CALCULATE(
    SUM('Sheet1'[Total]),
    'Date'[WeekNumCumulative] = week_num 
)

This, of course, requires you to have a Date table that includes a column WeekNumCumulative, that doesn't reset every year. This could be done by calculating the number of weeks between some start date and the date for the row in the date table. Here is an example of a Calculated Column in DAX:

 

WeekNumCumulative = FLOOR(DATEDIFF(date(2017,1,1),[Date],DAY) / 7,1)

The previous week measure would be almost identical to the current week, except:

    Date'[WeekNumCumulative] = week_num - 1

Hope this helps,

Nathan

 

 

 

 

 

  • cham's avatar
    cham
    Post Patron
    7 years ago

    Hi Anonymous 

     

    I tried the mesaure you mentioned but it did not work. 

     

    Here is my data set,

    Date SpendPay
    22-07-19589.32425.22
    24-07-193652.851833.72
    21-07-193659.52744.64
    24-07-192312.591726.67
    24-07-19519.59347.25
    24-07-1921178.4817955.15
    24-07-193016.453734.56
    22-07-192130.751562.65
    31-07-19107.5268.16
    31-07-196545.5
    31-07-191430.741078.24
    31-07-19108.315534.32
    01-08-191011.2310910.91

     

    I created a mesaure using this data set as below,

    %Amount = (SUM('Sheet1'[Spend]) - SUM('Sheet1'[Total Pay])) / SUM('Sheet1'[Spend])

     

    I want to get the diffrence between previous week %Amoun?with this week. How can i do that?Hope this will help you because i provide the data set.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      cham - 

      To compare 2 different time periods, you need to "modify the filter context". The way to do this is with CALCULATE function, like this:

      %Amount vs Previous Week = 
      var prev_week_num = MAX('Date'[WeekNumCumulative]) - 1 --Find the maximum week number associated with the relevant date and subtract one to get the previous week.
      var prev_week_value = CALCULATE(
          [%Amount], 
          'Date'[WeekNumCumulative] = week_num
      )
      return [%Amount] - prev_week_value
      I hope this helps. If it does, please Mark as a solution.
      I also appreciate Kudos.
      Nathan Peterson