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.

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
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.

 

gco's avatar
gco
Resolver II
6 years ago

Hi v-juanli-msft and Greg_Deckler 

 

Thank you.  I am sure i would be able to use your measures in my other project.

 

I have actually fixed my problem by taking the total of the previous months + current month.  I created a calculated column on my previous months table and had used the measure:  

 

Total_Prev_months = CALCULATE (SUM (Prev_Months[Balances]), FILTER (Prev_Months, Prev_Months[IsThisYear] = "YES"))

Total_Current_month = CALCULATE (SUM (Daily_Data[Balances]), FILTER (Daily_Data, Daily_Data[IsCurrentMonth] = "YES"))

 

Thank you again

Glen

  • gco's avatar
    gco
    Resolver II
    6 years ago

    The calculated columns are for the IsThisYear, and IsCurrentMonth.