Forum Discussion

gco's avatar
gco
Resolver II
6 years ago
Solved

Need help with measures

Hi there,   Could you please help me figure out how to create a measure that only displays the data from 01/31 up to 09/30 below? I cannot use a filter in the view because i will be replacing 10/3...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi gco 

    Create a measure

    Measure =
IF (
    MAX ( 'calendar'[year] ) = YEAR ( TODAY () )
        && MAX ( 'calendar'[month] ) < MONTH ( TODAY () ),
    CALCULATE (
        SUM ( Sheet8[value] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[year] = YEAR ( TODAY () )
                && 'calendar'[month] < MONTH ( TODAY () )
                && 'calendar'[Date] <= MAX ( 'calendar'[Date] )
        )
    )
)

    My calendar table

    calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

    add column

    monthend = ENDOFMONTH('calendar'[Date])
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.