cancel
Showing results for
Did you mean:
Helper I

## Measures to call YTD/QTD/L4W per retailer considering different data submission

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
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
7 REPLIES 7
Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper I

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

Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper I

thanks Frank - This sounds ok for 2 customers but if I have more than a hundred.

Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper I

Thanks Frank - that should do the job, thanks.

Helper I

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

Announcements