Measure to Return Current Week's Sunday Order Count

[jbcorlikow]

I'm setting up a series of cards that will reflect the orders for the current week by day. I'll have a card for each day Sunday through Saturday. I've just set up my Sunday card with the following DAX formula:

Orders This Sunday Count =
VAR CurrentWeekNumber = WEEKNUM(TODAY(), 1)
VAR ThisSunday =
    CALCULATE(
        MAX('orders'[order_fulfilled_at].[Date]),
        'orders'[DayNumber] = 1,
        'orders'[WeekNumber] = CurrentWeekNumber,
        ALL('orders') // Remove filters that may limit the dates being evaluated
    )
VAR Result =
    CALCULATE(
        COUNT('orders'[id]),
        'orders'[order_fulfilled_at].[Date] = ThisSunday,
        ALL('orders') // Again, ensure that all dates are considered
    )
RETURN
IF(
    ISBLANK(ThisSunday),
    0, // Return 0 if there is no data for ThisSunday
    IF(
        ISBLANK(Result),
        0, // Return 0 if the Result is blank
        Result
    )
)

I have  calculated columns for DayNumber (WEEKDAY([order_fulfilled_at], 1) and WeekNumber (WEEKNUM([order_fulfilled_at], 1). The values are populated as expected for these columns in my table view. For example, the day I'm attempting to show the order count for is Sunday, 11/5/2023 which has a WeekNumber of 45 and a DayNumber of 1. However, when I run the formula above in my measure, it returns a 0 when I was expecting 194.

What am I missing? Is Power BI associating 11/5 with week 44?

[AllisonKennedy]

@jbcorlikow  This would be super easy with a dimDate table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Then you wouldn't need so much DAX - just put slicer on the page for Week Number, and put 'filters on this visual' for each card for the day number. Add the measure for Count Orders to the card visual with the filter for day number.

Count Orders = 
COUNT( orders[id] )

You can add calculate and clear filters to the Count Orders measure if you need, but not sure what you're trying to do with clearing the date filters?

[jbcorlikow]

The dashboard that I'm building out will be on a display on our warehouse floor. Any sort of manual intervention like using sliders won't be an option. I already built out the average order counts for each day of the week that shows the averages for the last 3 months. What I'm attempting to do with this next section is display the orders for the current week by day that's refreshed daily so that the team see current orders vs our average. So, my current week Sunday card display 194 orders, Monday will show 141, Tuesday - 157, and today will show 0 or nothing since it's the current day and our data lags about a day. All of these stats are in individual cards so that they can be easily read from a distance while working in the warehouse.

The abundance of DAX code is likely due to the fact that I'm pretty new to using Power BI 🙂

[AllisonKennedy]

@jbcorlikow  It doesn't need to be a manual intervention slider - just set a page level filter for week offset = 0. 

My date table in the post link above doesn't have a week offset column, so you'll need to create one: 

https://community.fabric.microsoft.com/t5/Desktop/Week-offset-in-Power-Query/td-p/2149004

[jbcorlikow]

I don't think that I follow, but that's mostly because I'm learning all of this on the fly as I build out this dashboard. I'm including a screenshot of what I'm attempting to do. My current dashboard has two slicers, store_id which allows me to limit the stats to a single or multiple stores and order_fulfilled_at which is a relative date slider that's show the last 3 months worth of order data. 

The top row of cards are my averages for each day of the week which gives us a baseline to measure our current performance off of. The averages use a measure that I created called Avg Orders [day of week] where [day of week] is Sunday, Monday, Tuesday, etc.

The second row of cards are the cards that I'm trying to set up the current week of order data. I want these cards to automatically update with the new week's data as the week completes each day. I sync the data 4 times daily so it'll refresh on a similar cycle. 

I then have another measure that I'm using to dynamic chance the font colors of the current week cards. I've named this FontColor [day of week]. Each of these measures compares my Avg Orders measure with the current week measure and then change the color based on whether or not the value is over or under the baseline averages.