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/31 value with another.  So my main goal is to create another measure

 

Overall_Total = Running_Total_LastMonth + Oct31_total

 

Here is the measure i am using

Running_Total_LastMonth =

CALCULATE (

   SUM(values),

   FILTER(

       ALL('Date'[MonthEnd]),

      'Date'[MonthEnd] <= MAX('Date'[MonthEnd])  // i was trying to modify this so that it does not display current month data.  

)

)

 

Any help is greatly appreciated.

Thank you

Glen

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.

     

4 Replies

  • 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.