Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic filter on This Month vs Last Month Cost Measure

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!
 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Thanks guys, it was as easy as this:

     

    Cost This Month =
    CALCULATE(
    [Cost],
    FILTER(
    'Calendar',
    'Calendar'[YearMonth] = MAX('Calendar'[YearMonth])))
     
     
     

4 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks guys, it was as easy as this:

     

    Cost This Month =
    CALCULATE(
    [Cost],
    FILTER(
    'Calendar',
    'Calendar'[YearMonth] = MAX('Calendar'[YearMonth])))
     
     
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    and

     

    Cost Last Month =
    CALCULATE(
    [Cost],
    FILTER(
    'Calendar',
    'Calendar'[YearMonth] = MAX('Calendar'[YearMonth])-1))