Dynamic filter on This Month vs Last Month Cost Measure

[Anonymous]

I'm using a simple data source with with Cost and YearMonth in number format "20191, 20192, 20193". I want a dynamic "Cost This Month" and "Cost Last Month" measure that updates every month.

I have created two measures "This Month" and "Previous Month":

This Month = MAX(Sheet1[YearMonth])

Last Month = [This Month] -1

And then I created the Cost Measures, where the hard codes ones work perfectly. However I need them to be dynamic.

So this hardcoded measure is working just fine: 

1. Cost This Month = CALCULATE(SUM(Sheet1[Cost]), Sheet1[YearMonth] = 20192)

This dynamic measure using the [This Month] measure is summarizing all cost in the table instead of filtering:

2. Cost This Month = CALCULATE( SUM( Sheet1[Cost]), FILTER( Sheet1, Sheet1[YearMonth] = [This Month]))

The Cost Last Month measure is not giving me any data at all. But for some reason when I change the [Last Month] measure to a hardcoded "20191" this measure is working as expected:

3. Cost Last Month = CALCULATE( SUM( Sheet1[Cost]), FILTER( Sheet1, Sheet1[YearMonth] = [Last Month]))

So what's going on here, can I create these Cost Measures by using a dynamic filter with my YearMonth values?

Thanks!

[Anonymous]

and

Cost Last Month =

CALCULATE(

[Cost],

FILTER(

'Calendar',

'Calendar'[YearMonth] = MAX('Calendar'[YearMonth])-1))

[Anonymous · Accepted solution]

Thanks guys, it was as easy as this:

Cost This Month =

CALCULATE(

[Cost],

FILTER(

'Calendar',

'Calendar'[YearMonth] = MAX('Calendar'[YearMonth])))

[Ashish_Mathur]

Hi,

We will have to create a Date column from the Year Month column.  We will then create a Calendar Table and build a relationship from the Date column of your base data sheet to the Date column of your Calendar Table.  In the Calendar Table, we will then write calculated column formulas to extract the Year and Month.  Once this is done, we can write simple measures to get your desired result.

[v-piga-msft]

Hi @Anonymous ,

Assuming that you have the data sample like below.

Then you could create the measure below. You could modify the measure based on your actual requirement.

Measure =
CALCULATE (
    SUM ( 'Table1'[Cost] ),
    FILTER ( 'Table1', 'Table1'[YearMonth] = MAX ( 'Table1'[YearMonth] ) )
)
Measure 2 =
CALCULATE (
    SUM ( Table1[Cost] ),
    FILTER (
        ALLSELECTED ( 'Table1' ),
        'Table1'[YearMonth]
            = IF (
                RIGHT ( FORMAT ( MAX ( 'Table1'[YearMonth] ), "" ), 1 ) = "1",
                BLANK (),
                MAX ( 'Table1'[YearMonth] ) - 1
            )
    )
)

Here is the output.

Best Regards,

Cherry