Forum Discussion

emmaclarke83's avatar
emmaclarke83
Helper I
8 years ago
Solved

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 addi...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    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,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

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,