cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Time Date Measures

@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
Moderator v-sihou-msft
Moderator

Re: Time Date Measures

@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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors