Forum Discussion

serax's avatar
serax
Frequent Visitor
4 years ago

Problem with comparing two CALCULATEDTABLES & with MAX WEEK

Dear Community,

Firstly, I would like to show some sample data table and it's format. These are testing data on which I would like to explain my problem:

Task description: One slicer has to be used, field:  'Table[Date(YYYY-WW)]), comparing two weeks: current week - it's a week selected by an user on the slicer versus previous week - (current week -1).  I have to use pie chart to show amount of IDs by two conditions:

  • Increase: If ID existed in two compared weeks and 'Table[Value] in current week (selected) is greater than in previous week (selected -1)
  • Decrease: If ID existed in two compared weeks and 'Table[Value] in current week (selected) is lower than in previous week (selected 1)

According to upper table, there would be 1 ID in "Increase" group: C - because it appeared in two analyzed weeks + value in selected week is greater than previously and 1 ID in "Decrease" group: A -because it appeared in two analyzed weeks + value in selected week is lower than previously.

 

Problem with max week from previous year:

I wrote couple of  measures which are used to filter the table:

  1. VAR year = SELECTEDVALUE('Table'[Year])
  2. VAR week = SELECTEDVALUE('Table'[Week])
  3. VAR MAX_WEEK = CALCULATE(MAX('Table'[Week]),ALL('Table'))
  4. VAR current_week = SELECTEDVALUE('Table'[Date(YYYY-WW)])
  5. VAR previous_week = CALCULATE(SELECTEDVALUE('Table'[Date(YYYY-WW)]), FILTER(ALL('Table'), IF(WEEK = 1, 'Table'[Week] = MAX_WEEK && 'Table'[Year] = year -1, 'Table'[Week] = week - 1 && 'Table'[Year] = year)))

Unfortunately, previous_week measure doesn't work correctly, MAX week value in whole table is 53, if previous year has lower max week value than 53, measure returns blank value (if previous year has a max value equal to the max value from whole table, it works perfectly).

This is the first problem with which I am counting on your help.

 

Problem with comparing two CALCULATEDTABLES:

I wrote two measures, which create two "virtual" tables in filtered weeks, but unfortunately I have no idea how to compare them, so I can obtain desired results:

  1. VAR CURRENT_WEEK_TABLE = CALCULATETABLE(SUMMARIZE('Table','Table'[ID], 'Table'[Value]), 'Table'['Table'[Date(YYYY-WW)] = current_week)
  2. VAR PREVIOUS_WEEK_TABLE = CALCULATETABLE(SUMMARIZE('Table','Table'[ID], 'Table'[Value]), 'Table'['Table'[Date(YYYY-WW)] = previous_week)

I will be really grateful for any form of help provided!

Need Help

3 Replies