cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mim
Advocate V
Advocate V

group by dynamic week

I have this calendar that calculated week based on a particular weekend, which is the last date from another table using this measure "Cutt off daily", everything was fine, untill someone wanted a slicer but as you know calculated column can not change calcultion based on another slicer or filter, is there any workaround

weekend.JPG

 

 
1 ACCEPTED SOLUTION
Brian_M
Responsive Resident
Responsive Resident

Note that the Calendar table has to be marked as a date table for the time intelligence (DATESBETWEEN) functions to work.

 

Based on your response on twitter, the pbix file also has a YTD version for the sum of qty.

 

Hope that helps!

View solution in original post

6 REPLIES 6
Brian_M
Responsive Resident
Responsive Resident

I undesrtand you wish to group all of the sales for each given week into a seven day period ending on the same day of the week as the selected slicer.

 

The measure below checks to see if our date on the X-axis is the same day of the week as your slicer. If not it returns BLANK( ), if it is the same day of week, it sums the qty for the previous 7 days.

 

Qty at 'week end' =
VAR SelectedSlicerDate =
    SELECTEDVALUE ( 'Slicer Dates'[Date] )
VAR ThisDateOnAxis =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    IF (
        WEEKDAY ( SelectedSlicerDate ) <> WEEKDAY ( ThisDateOnAxis ),
        BLANK (),
        CALCULATE (
            SUM ( 'Data Table'[qty] ),
            DATESBETWEEN ( 'Calendar'[Date], ThisDateOnAxis - 6, ThisDateOnAxis )
        )
    )

 

I'll attempt to link to a copy of the pbix

Example PBIX file 

Brian_M
Responsive Resident
Responsive Resident

Note that the Calendar table has to be marked as a date table for the time intelligence (DATESBETWEEN) functions to work.

 

Based on your response on twitter, the pbix file also has a YTD version for the sum of qty.

 

Hope that helps!

View solution in original post

Brian_M
Responsive Resident
Responsive Resident

Is there an example file we can have a crack at, would love to give it a go!

v-xicai
Community Support
Community Support

Hi @mim ,

 

You couldn't have a calculated column interacted with a slicer or interaction , since columns are only calculated when data is loaded/refreshed.  So you may need to click "Refresh" button for every time you make filter in slicer or interact with other visual.

 

In addition, you may create the [dynamic Week End] using measure instead of calculated column like DAX below, While it may not be displayed in Data view, you may show it in table visual using fields as you need.

 

dynamic Week End= MAX([Date]) -MOD(MAX([Date]) - WEEKDAY([Cutt off daily], 2) -2, 7)+6

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@mim ,Please if my blog can help you for week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak  my case is different, my weekend is not fixed, basically, I want the weekend to change based on another measure

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.