cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nickpw
Frequent Visitor

DAX ALL not working as expected using date

Hi,

 

I have a measure that aggregate sales for the last seven days, see below. I want this to ignore the date slicer. However that isn't happening. The value should be £220.77K for the last seven days.  But if I change the date slicer the value changes and is instead showing the last seven day value from the 06.09.2021.

 

Can anyone help me understand why this is happening?

 

Measure = CALCULATE (
    'Measures Table'[Sum_Gross_Sales],
    DATESBETWEEN (
        'Calendar'[Date],
        MAX ( 'Calendar'[Date] ) - 7,
        MAX ( 'Calendar'[Date] ) - 1
    ), ALL('Calendar'[Date])
)

 

Screenshot 2022-08-09 115125.png

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

The ALL('Calendar'[Date]) is not being applied to the DATESBETWEEN function, it is only being applied to the [Sum_gross_sales] measure, so when you call MAX('Calendar'[Date]) the slicer is still in effect. 

If your Calendar table is marked as a date table then you don't need to remove filters from the 'Calendar'[Date] column if you apply another filter to that column, DAX takes care of that automatically. Try using

Last 7 days = CALCULATE( [Sum_Gross_Sales], DATESBETWEEN( 'Calendar'[Date], TODAY() -7, TODAY() - 1) )

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

Could you please try the below measure if it shows the outcome that is expected?

 

Measure: =
VAR _lastdateincalendartable =
    CALCULATE ( MAX ( 'Calendar'[Date] ), REMOVEFILTERS () )
RETURN
    CALCULATE (
        'Measures Table'[Sum_Gross_Sales],
        DATESBETWEEN (
            'Calendar'[Date],
            _lastdateincalendartable - 7,
            _lastdateincalendartable - 1
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


johnt75
Super User
Super User

The ALL('Calendar'[Date]) is not being applied to the DATESBETWEEN function, it is only being applied to the [Sum_gross_sales] measure, so when you call MAX('Calendar'[Date]) the slicer is still in effect. 

If your Calendar table is marked as a date table then you don't need to remove filters from the 'Calendar'[Date] column if you apply another filter to that column, DAX takes care of that automatically. Try using

Last 7 days = CALCULATE( [Sum_Gross_Sales], DATESBETWEEN( 'Calendar'[Date], TODAY() -7, TODAY() - 1) )

Thank you John, that solved my issues.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.