Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wagrezy
Helper I
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

 

CustomerYearWeek NumberSales
Cust AN-1150
Cust BN-11100
Cust AN-12150
Cust AN-1350
Cust BN-12100
Cust BN-13150
Cust AN1200
Cust BN150
Cust AN2100
Cust AN350
Cust BN2200
1 ACCEPTED 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 ) )
    )

3.PNG

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.

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
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))

Capture.PNG

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.

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
                )
            )
    )

2.PNG

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.

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 ) )
    )

3.PNG

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.