Time Date Measures

[emmaclarke83]

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
        )
    )

[v-sihou-msft · Accepted solution]

@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,