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
emmaclarke83
Helper I
Helper I

Time Date Measures

I am trying to calculate values for Week, Period and Year on a custom calendar.  I have created 3 measures to get the Start of the FYear, Period and Week.  And then used these values to create 3 additional measures for calculating the total for Year, Period and Week, depending what has been selected on the date slicer.  I have also 3 slicers to filter the date.  One for Year, one for period and one for week.

All these measures work fine on their own, and when trying to use them together, they are affecting each other.  For example, if I select the financial year filter, my measure calculates the year to date total, but when I start filtering on period and week, it changes to the period or week value.  How do I get each slicer to be independent?  I want these values to appear on cards.  So one for Week, one for Period and one for Year.  There are other visuals on the page that also need to work like tables and charts based on the week selected on the slicer but these measures for the cards need to independent.  So I select the Fy as 17-18, the year card will show be the YTD value.  Then if I select the Period, the period card measure works but the year card shows YTD etc.

My next step would be to show the values for the previous year if you could also help on this?

 

Thanks

 

Here are the measures

Start Day of Year =
VAR currentDay =
    MAX ( 'dates'[date] )
VAR currentYear =
    CALCULATE (
        MAX ( 'dates'[year] ),
        'dates'[date] = currentDay
    )
RETURN
    CALCULATE (
        MIN ( 'dates'[date] ),
        FILTER (
            ALL ( 'dates' ),
            'dates'[year] = currentYear
        )
    )

 

 

Start Day of Period =
VAR currentDay =
    MAX ( 'dates'[date] )
VAR currentPeriod =
    CALCULATE (
        MAX ( 'dates'[period] ),
        'dates'[date] = currentDay
    )
RETURN
    CALCULATE (
        MIN ( 'dates'[date] ),
        FILTER (
            ALLSELECTED ( 'dates' ),
            'dates'[period] = currentPeriod
        )
    )

 

Start Day of Week =
VAR currentDay =
    MAX ( 'dates'[date] )
VAR currentWeek =
    CALCULATE (
        MAX ( 'dates'[week] ),
        'dates'[date] = currentDay
    )
RETURN
    CALCULATE (
        MIN ( 'dates'[date] ),
        FILTER ( ALLSELECTED ( 'dates' ), 'dates'[week] = currentWeek )
    )

 

 

Collisions Year =
VAR currentDay =
    MAX ( 'dates'[date] )
RETURN
    CALCULATE (
        [Total Collisions],
        FILTER (
            'dates',
            'dates'[date] >= [Start Day of Year]
                && 'dates'[date] <= currentDay
        )
    )

 

Collisions Week =
VAR currentDay =
    MAX ( 'dates'[date] )
RETURN
    CALCULATE (
        [Total Collisions],
        FILTER (
            'dates',
            'dates'[date] >= [Start Day of Week]
                && 'dates'[date] <= currentDay
        )
    )

 

Collisions Period =
VAR currentDay =
    MAX ( 'dates'[date] )
RETURN
    CALCULATE (
        [Total Collisions],
        FILTER (
            'dates',
            'dates'[date] >= [Start Day of Period]
                && 'dates'[date] <= currentDay
        )
    )

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@emmaclarke83

 

In this scenario, when you filter on Period or Week, the filter context will change to a Period or Week, that's the reason why you YTD measure will return the Period-to-Date or Week-to-Date value. For your requirement, you should add ALL() in your FILTER() to ignore the selection from filter/slicer.

 

Collisions Year =
VAR currentDay =
    MAX ( 'dates'[date] )
RETURN
    CALCULATE (
        [Total Collisions],
        FILTER (
            ALL('dates'),
            'dates'[date] >= [Start Day of Year]
                && 'dates'[date] <= currentDay
        )
    )

 

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@emmaclarke83

 

In this scenario, when you filter on Period or Week, the filter context will change to a Period or Week, that's the reason why you YTD measure will return the Period-to-Date or Week-to-Date value. For your requirement, you should add ALL() in your FILTER() to ignore the selection from filter/slicer.

 

Collisions Year =
VAR currentDay =
    MAX ( 'dates'[date] )
RETURN
    CALCULATE (
        [Total Collisions],
        FILTER (
            ALL('dates'),
            'dates'[date] >= [Start Day of Year]
                && 'dates'[date] <= currentDay
        )
    )

 

 

Regards,

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.