Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.