Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am quite new to advanced DAX and would appreciate any help.
So my situation is I have a data set with multiple customer/retailer sales data by week. They are not necessarily updated at the same time.
So in the example below, on year N, data were submitted for customer A up to week 3 but for customer B, they were only submitted until week 2. Data of year N-1 can be considered as complete.
I would like to create YTD / QTD / last 4 weeks measures which will take this difference into consideration.
So again, in the example below my YTD for customer A will automatically call weeks 1 to 3 for both years but will call week 1 to 2 for customer B.
I hope that makes sense.
Thanks. Yoann
Customer | Year | Week Number | Sales |
Cust A | N-1 | 1 | 50 |
Cust B | N-1 | 1 | 100 |
Cust A | N-1 | 2 | 150 |
Cust A | N-1 | 3 | 50 |
Cust B | N-1 | 2 | 100 |
Cust B | N-1 | 3 | 150 |
Cust A | N | 1 | 200 |
Cust B | N | 1 | 50 |
Cust A | N | 2 | 100 |
Cust A | N | 3 | 50 |
Cust B | N | 2 | 200 |
Solved! Go to Solution.
Hi @wagrezy,
Then we can create the measures as below to achieve your goal.
Measure 2 = VAR maxyear = CALCULATE ( MAX ( Table1[Year] ), ALL ( Table1 ) ) VAR maxweek = CALCULATE ( MAX ( Table1[Week Number] ), FILTER ( ALL ( Table1 ), Table1[Year] = maxyear ), VALUES ( Table1[Customer] ) ) RETURN IF ( MAX ( Table1[Week Number] ) <= maxweek && MAX ( Table1[Year] ) = maxyear - 1, 1, 0 )
Measure 3 = VAR maxyear = CALCULATE ( MAX ( Table1[Year] ) ) VAR maxweek = CALCULATE ( MAX ( Table1[Week Number] ), FILTER ( Table1, Table1[Year] = maxyear ) ) RETURN IF ( ISFILTERED ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Week Number] <= maxweek && Table1[Year] = maxyear - 1 ) ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, [Measure 2] = 1 ) ) )
For more details, please check the pbix as attached.
Regards,
Frank
Hi @wagrezy,
We can create a measure as below to achieve your goal using your sample data.
Measure = var maxyear = CALCULATE(MAX(Table1[Year])) var maxweek = CALCULATE(MAX(Table1[Week Number]),FILTER(Table1,Table1[Year]=maxyear)) return CALCULATE(SUM(Table1[Sales]),FILTER(Table1,Table1[Week Number]<=maxweek && Table1[Year]=maxyear-1))
For more details, please check the pbix as attached.
Regards,
Frank
Many thanks, very helpful.
The solution you suggested will be filtered on year n-1 ; i suppose i just have to remove the '&& Table1[Year]=maxyear-1' into the CALCULATE formula to get the result on all years.
However, when I do so it returns 350 for 2016 instead of displaying 200. However the total 2016+2017 is 450. So it works for the total but not individually for each.
So i suppose one of the solution would be to dupplicate the formula: one for 2016 and one for 2017.
Yoann
Hi @wagrezy,
Then we can only create the measure as below.
Measure = VAR maxyear = CALCULATE ( MAX ( Table1[Year] ) ) VAR maxweek = CALCULATE ( MAX ( Table1[Week Number] ), FILTER ( Table1, Table1[Year] = maxyear ) ) VAR A = CALCULATE ( MAX ( Table1[Week Number] ), FILTER ( Table1, Table1[Year] = maxyear && Table1[Customer] = "Cust A" ) ) VAR B = CALCULATE ( MAX ( Table1[Week Number] ), FILTER ( Table1, Table1[Year] = maxyear && Table1[Customer] = "Cust B" ) ) RETURN IF ( ISFILTERED ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Week Number] <= maxweek && Table1[Year] = maxyear - 1 ) ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Year] = maxyear - 1 && Table1[Customer] = "Cust A" && Table1[Week Number] <= A ) ) + CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Year] = maxyear - 1 && Table1[Customer] = "Cust B" && Table1[Week Number] <= B ) ) )
Regards,
Frank
thanks Frank - This sounds ok for 2 customers but if I have more than a hundred.
Hi @wagrezy,
Then we can create the measures as below to achieve your goal.
Measure 2 = VAR maxyear = CALCULATE ( MAX ( Table1[Year] ), ALL ( Table1 ) ) VAR maxweek = CALCULATE ( MAX ( Table1[Week Number] ), FILTER ( ALL ( Table1 ), Table1[Year] = maxyear ), VALUES ( Table1[Customer] ) ) RETURN IF ( MAX ( Table1[Week Number] ) <= maxweek && MAX ( Table1[Year] ) = maxyear - 1, 1, 0 )
Measure 3 = VAR maxyear = CALCULATE ( MAX ( Table1[Year] ) ) VAR maxweek = CALCULATE ( MAX ( Table1[Week Number] ), FILTER ( Table1, Table1[Year] = maxyear ) ) RETURN IF ( ISFILTERED ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Week Number] <= maxweek && Table1[Year] = maxyear - 1 ) ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, [Measure 2] = 1 ) ) )
For more details, please check the pbix as attached.
Regards,
Frank
Thanks Frank - that should do the job, thanks.
Thanks Frank - I will try this. All this is very helpful anyway. I am starting to understand how all this works. Quick question, when I define a variable in a measure is it only available in this measure or can I call it whilst creating other measures.
I'll keep you posted on how it goes.
Regards,
Yoann
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |