Evaluating dates in the past

[Anonymous]

Dear all,

I have a set of data that has to be evaluated taking in account the previous month.

I am presenting a mock dataset (normal month/value columns) and the desired output.
I need a measure that for each month it evaluates itself PLUS a function of the previous month.



I can't manage this situation as the filters block me to evaluate the previous months.

I appreciate your help,
V

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

As the screenshot you shared, we should convert the month column from text to number by creating a calculated column.

MonthNumber = 
SWITCH (
    'Dataset'[Month],
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12
)

Then we can create the following measure to meet what you need.

CurrentMonth =
SUM ( 'Dataset'[Value] )

NextMonth/2 =
FORMAT (
    CALCULATE (
        SUM ( 'Dataset'[Value] ),
        FILTER (
            ALL ( 'Dataset' ),
            'Dataset'[MonthNumber]
                = SELECTEDVALUE ( 'Dataset'[MonthNumber] ) + 1
        )
    ) / 2,
    "General Number"
)

Total =
IFERROR ( [CurrentMonth] - [NextMonth/2], BLANK () )

BTW, pbix as attached.