Any way to get a measure that calculate previous weeks average from becomes 0 if selected week is 0?

[sosVikar]

Hi everyone

I am trying to create a way to compare orders made on a selected week with the average of the previous 5 weeks per client. This works exactly as expected when a client has made orders on a selected week, but if a client have made zero orders any measure that tries to fetch data from another week or date becomes zero. The image below tries to shows the problem in a graph.

Below is the measure I use to get the previous week that becomes zero if the selected week has zero orders.

CALCULATE(
    COUNTROWS('orders'),
    FILTER(
        ALL('orders'),
        'orders'[week]=MAX('orders'[week])-1
    ),
    FILTER(
        ALL('orders'),
        'orders'[year]=MAX('orders'[year])
    ),
    FILTER(
        ALL('clients'),
        'clients'[client_lvl0]=MAX('clients'[client_lvl0])
        /* This filter is needed to make Power BI able to separate orders by clients when fetching data from another week. Without the filter each client gets the previous week total.  */
    )
)

Any kind of help will be greatly appreciated.

[sosVikar · Accepted solution]

Thanks @rajendraongole1 and @v-cgao-msft  for trying to help, and pionting me in the direction for the solution I ended up with.

I already have a seperate Date table, but for some reason there where problems with week numbers. The problem perhaps comes perhaps from the data used is import from a MySQL DB that stores time as the datatype Int instead of DateTime.

The solution for me where to add an additional colum in the SELECT command for the MySQL import that added the date of the last day in a week. Then I where able get the correct data in the graph with the below filter:

FILTER(
  ALL('Orders'),
  'Orders'[lastDayOfAWeek]<MAX('Date'[Date]) &&
  'Orders'[lastDayOfAWeek]>MAX('Date'[Date])-42
)

If someone are the in same situation the way to get the date of the last day in a week in MySQL are something like this:

STR_TO_DATE(concat(year(from_unixtime(dateAsInt)), weekofyear(from_unixtime(dateAsInt)),' sunday'), '%X%V %W') as lastDayOfAWeek

[v-cgao-msft]

Hi @sosVikar ，

I also agree that a calendar table is needed in the model to handle weeks that span years.

This way you can find out the date range using the maximum date of the week - 41days, like this:

Measure = 
VAR __cur_orders = [Total orders]
VAR __week_end_date = MAX('Date'[WeekEndDate])
VAR __min_date = __week_end_date - 41
VAR __max_date = __week_end_date - 7
VAR __average = AVERAGEX( FILTER( ALL('Date'), 'Date'[Date]>=__min_date && 'Date'[Date]<=__max_date), CALCULATE([Total orders],ALLEXCEPT('Date','Date'[Week])) )
VAR __result = IF( NOT ISBLANK( [Total orders] ), [Total orders], __average)
RETURN
__result

Week-related calculations – DAX Patterns

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[rajendraongole1]

Hi @sosVikar - Please create a seperate date table and mark it as date table in your model.

Then create to calculate Total Orders for the Selected Week  measure:

TotalOrdersSelectedWeek =
CALCULATE(
COUNTROWS('orders'),
FILTER(
'orders',
'orders'[week] = MAX('DateTable'[Week]) &&
'orders'[year] = MAX('DateTable'[Year])
)
)

Calculate Average Orders for the Previous 5 Weeks : I am using date table here please add your date table and columns as per your convinence 

AverageOrdersPrevious5Weeks =
CALCULATE(
AVERAGEX(
VALUES(DateTable[Week]),
CALCULATE(
COUNTROWS('orders'),
FILTER(
'orders',
'orders'[week] IN
SELECTCOLUMNS(
FILTER(
ADDCOLUMNS(
VALUES(DateTable[Week]),
"WeekOffset",
RANKX(ALL(DateTable[Week]), DateTable[Week],, ASC, DENSE)
),
[WeekOffset] <= RANKX(ALL(DateTable[Week]), MAX(DateTable[Week]),, ASC, DENSE) - 1 &&
[WeekOffset] > RANKX(ALL(DateTable[Week]), MAX(DateTable[Week]),, ASC, DENSE) - 6
),
"WeekOffset", DateTable[Week]
)
),
FILTER(
'orders',
'orders'[year] = MAX('DateTable'[Year])
),
FILTER(
'clients',
'clients'[client_lvl0] = MAX('clients'[client_lvl0])
)
)
)
)

use both measures in your visual to compare the selected week's orders with the average of the previous 5 weeks and handle cases where clients have zero orders in the selected week

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!