Problem with comparing two CALCULATEDTABLES & with MAX WEEK

[serax]

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!

[amitchandak]

@serax , for this, it is best to create a separate year week table with year week (YYYY-WW), year and week. Say date

have rank column in date table

Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[serax]

Hello @amitchandak
Your proposed solution by using RANK solved my MAX_WEEK issue - thanks! But still I have no clue how to compare two calculated tables. 

Best regards!

[v-shex-msft]

HI @serax,

Perhaps you can try to use ADDCOLUMNS and LOOKUPVALUE functions to add new table columns to the current table based on id.

ADDCOLUMNS function (DAX) - DAX | Microsoft Learn

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

Regard,

Xiaoxin Sehng