Dynamically calculating "7 days bucket"

DAX DAX Calculations
1

Hi, I need to dynamically calculating “7 days bucket” based on a date range (date filter)

See below:
TheDate column contains AllSelected() dates from my date filter.
And I need to write a dax query to produce “dynamic Week” column value. Where I always start counting the 1st day selected as my 1 day of the week… and counting 7 days for a week… and assign “week #” bucket accordingly.

I am stuck. Not sure where and how to start. Could someone guide me ?

image

Here is the pbix file to play with.
PAS-New- AccessNumber attempt - added open orders compute.pbix (1.0 MB)

2

Ok regarding your pbix file. I have basically no idea what you’re referring to because there’s nothing in the report?

image
image1103×599 25.7 KB

What formula are you refering to?

What are you trying to show?

What is the visual you’re ultimately after?

Please make it more intuitive what you need…

Also, have you gone through the weekly time intelligence videos that are made available. They show the many ways you can run time comparison logic with weeks.

They are located below.

Thanks
Sam

3

Good Morning Sam.
Yes, watched those training videos before, but not helping.

Just as my example shown above, I need to dynamically calculate the week not based on the time intelligent function, or week number, but always start counting the 1st day selected as day 1, 2nd day selected = day 2 ,… so forth till day 7, this bucket = Week 1 bucket.

The end visual is a simple bar graph where X-axis is “Week 1”, “week 2” … Y -axis = # orders.
Sample chart: imagine the X-Axis is being replaced with Week 1, Week 2, Week 3… etc

image
image.png771×161 4.34 KB

I don’t know how to write the DAX, hence don’t have any visuals or formulas to share in my PBIX file. But just some sample data to try some formulas.

Let me know if there is anything unclear here.

4

Your first request could be done with something like:

 Dynamic week = 
    VAR myFirstDate = 
        CALCULATE( MIN( 'Calendar'[theDate] )|
            ALLSELECTED( 'Calendar'[theDate] )
        )
    VAR myCounter =
        INT( DIVIDE( INT( MAX( 'Calendar'[theDate] ) - myFirstDate ) | 7 ) +1 )
    RETURN

    IF( HASONEVALUE( 'Calendar'[theDate] ) | "Week "&myCounter )

As you can see in this measure you need the Calendar’[theDate] value to calculate the [Dynamic week] measure for each row, so in your first scenario this would work fine.
But in your second scenario this won’t do for two reasons first you’re missing the Calendar’[theDate] but more important you cannot place a measure on the Axis of the bar chart…

Also you can’t solve this by adding a calculated column to your Calandar table if your date selection is truly dynamic because that will only get evaluated once at refresh time.

So I think you might need to explore alternatives or consider standard weeknumbers.

5

Hi Melissa, thanks for your sharing. This is awesome !!
Yes, you are right, I am not able to ‘anchor’ the Dynamic Week to my bar chart. But it allows me to move to next step. :slight_smile: Let me play with a summary table by ‘anchoring’ the data to chart with MIN(‘Calendar’[theDate] ) instead of text = Week 1, Week 2.

6

Update, (yes, Melissa I need to propose the weekNumbers to product manager. )

I am able to show the data in chart if I decided to go with “1 date” = Min(theDate) by blank() out the other days.

But I lost the interactivity to the detail tables.

before selecting on my Min(date) bar chart sample:-

image
image.png1268×618 80.6 KB

Selecting Min(date) on my bar chart - I only can see 1 day of details. (should be 7 days)

image
image.png1444×370 40.9 KB