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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sosVikar
Regular Visitor

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

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.

 

graph.png

 

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.

1 ACCEPTED SOLUTION
sosVikar
Regular Visitor

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

 

 

View solution in original post

3 REPLIES 3
sosVikar
Regular Visitor

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
Community Support
Community Support

Hi @sosVikar ,

 

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

vcgaomsft_0-1718001620641.png

 

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

vcgaomsft_1-1718002549271.png


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
Community Champion
Community Champion

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.