Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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)
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)
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)
use DATEADD function and put the interval -1 & WEEK
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |