cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Different result for date calculation using date slicer with or without hierarchy

Hi,
I've tried searching, but not seen this anywhere...
I'm building a report, and want to keep the date slicer small and simple, so I used a relative date slicer, which also updates to the current date, so that was nice. But when I then add a last year and last week measure, the result is that it will go back in time and show me the current day result. I found this to be linked to the slicer using date hierarchy or not.

Without hierarchy:

no hierarchy1.png

It seems the calculation goes back in time as requested, then shows the number for the selected date. 

 

With a hierarchy in the slicer:

with hierarchy.png

This is the result I was expecting, no matter if the hierarchy was added to the slicer.

 

Is there anyway to use the relative time slicer and still get the result as with the hierarchy?

 

Thanks

 

Øystein

1 ACCEPTED SOLUTION

Hi @Anonymous ,

If your date column is not continuously, not recommend to use Time-intelligence function like dateadd(), sameperiodlastyear() etc.

Based on your description, you can create a control measure like this, put it in the visual filter and set its value as 1:

Control = 
VAR _max =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _min =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) >= _min
            && SELECTEDVALUE ( 'Table'[Date] ) <= _max,
        1,
        0
    )

filter.png 

Then you can create measures like this:

now = SUM('Table'[value])
prev = 
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date]
            >= MIN ( 'Table'[Date] ) - 7
            && 'Table'[Date] <= MIN ( 'Table'[Date] )
    )
)
ly = 
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] )
            = YEAR ( MIN ( 'Table'[Date] ) ) - 1
            && QUARTER ( 'Table'[Date] ) = QUARTER ( MIN ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( MIN ( 'Table'[Date] ) )
            && DAY ( 'Table'[Date] ) = DAY ( MIN ( 'Table'[Date] ) )
    )
)

result.png

Attached a sample file in the below ,hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi,
Thanks for your reply. I'll check it out.

 

Øystein

Anonymous
Not applicable

Hi,
sorry, did not add the calculations:

appointment_counter_ly = CALCULATE(sum(sight_tests_details[appointment_counter]),SAMEPERIODLASTYEAR(sight_tests_details[appointment_date].[Date]))
 
appointment_prev7day = CALCULATE(sum(sight_tests_details[appointment_counter]),DATEADD(sight_tests_details[appointment_date].[Date], -7, DAY))
 
I don't think they're the problem, it's that they behave differently depending on the slicer...
 
Thanks for your reply.
 
Øystein

Hi @Anonymous ,

If your date column is not continuously, not recommend to use Time-intelligence function like dateadd(), sameperiodlastyear() etc.

Based on your description, you can create a control measure like this, put it in the visual filter and set its value as 1:

Control = 
VAR _max =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _min =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) >= _min
            && SELECTEDVALUE ( 'Table'[Date] ) <= _max,
        1,
        0
    )

filter.png 

Then you can create measures like this:

now = SUM('Table'[value])
prev = 
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date]
            >= MIN ( 'Table'[Date] ) - 7
            && 'Table'[Date] <= MIN ( 'Table'[Date] )
    )
)
ly = 
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] )
            = YEAR ( MIN ( 'Table'[Date] ) ) - 1
            && QUARTER ( 'Table'[Date] ) = QUARTER ( MIN ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( MIN ( 'Table'[Date] ) )
            && DAY ( 'Table'[Date] ) = DAY ( MIN ( 'Table'[Date] ) )
    )
)

result.png

Attached a sample file in the below ,hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
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

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors