Comparing values based on days of two weeks

[stefani_vileva]

Hello everyone,

I have a question about calculating something. 

The problem is following. I have one date table in which I have Date, Week and Week rank, and I have another table called orders which contain Order Date, Product, Type of Product, Quantity ordered. Both of them are connected based on the Order Date.

My goal is to have the following table:

Product	Monday Prev | Curr	Tuesday Prev | Curr	Wednesday Prev | Curr	Thursday Prev | Curr	Friday Prev | Curr	Saturday Prev | Curr	Sunday Prev | Curr
Article A   type x   type y	100 | 120 30 | 90 70 | 30	130 | 100 70 | 70 60 | 30	...	...	...

| is meaning that that is drilled down for more details.

The calculated measures are as follows:

Current (KG) = CALCULATE(SUM(Orders[QUANTITY_ORDERED]), 'Date'[Week rank]= MAX('Date'[Week rank]))

Previous (KG) = CALCULATE(SUM(Orders[QUANTITY_ORDERED]), 'Date'[Week rank]= MAX('Date'[Week rank])-1)

The problem that I am having is wrong calculation. For example if today is Tuesday, the column of current for the future days (Wednesday, Thursday.. ) should be 0, but in my case I have values.

Can please someone explain to me why is this happening?

Thank you very much.

Best,

Stefani

[stefani_vileva · Accepted solution]

I have solved my problem. The problem was in the dates, the date table was filled only with dates until today, so basically for the future I did not have any dates and in the background for example if today is wednesday and I want to see how much orders I want for Friday is not possible because Friday doesn't exist in the table. So in order to get the quantity just of the previous week, I needed to change the formulas. The new calculations are:

Current (KG) = VAR max_rank = CALCULATE(MAX('Date'[Week rank]), ALL('Date'))
RETURN 
    CALCULATE(SUM(Orders[QUANTITY_ORDERED]), 'Date'[Week rank]= max_rank)

Previous (KG) = 
VAR prev_max_rank = CALCULATE(MAX('Date'[Week rank]), ALL('Date'))-1
RETURN 
    CALCULATE(SUM(Orders[QUANTITY_ORDERED]), 'Date'[Week rank]= prev_max_rank)

[stefani_vileva]

Hello @johnt75 , @mh2587,

Unfortunately, I still have the same problem with the calculation if the current quantity orders. It looks like I am ordering for the future which is impossibles, but thanks anyway.

Best,

Stefani

[johnt75]

Do you have data for last year? If so then that could be being included as there doesn't appear to be a filter on year.

You could try

Previous (KG) = 
var prevWeek = LOOKUPVALUE( 'Date'[Week rank], 'Date'[Date], TODAY() - 7)
return CALCULATE(SUM(Orders[QUANTITY_ORDERED]), 'Date'[Week rank]= prevWeek)

Current (KG) = 
var currentWeek = LOOKUPVALUE( 'Date'[Week rank], 'Date'[Date], TODAY())
return CALCULATE(SUM(Orders[QUANTITY_ORDERED]), 'Date'[Week rank]= currentWeek)

[mh2587]

use DATEADD function and put the interval -1 & WEEK