Need help with measures

[gco]

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

[v-juanli-msft · Accepted solution]

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]

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]

The calculated columns are for the IsThisYear, and IsCurrentMonth.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...