How to create measures for the previous month and same month last year?

[Nsimpson]

When I try to create the below measure I get the following error.

TEST Prev Month = CALCULATE(SUM('tbl6PakData_Archive'[Inv6Pak]),DATEADD('tbl6PakData_Archive'[Month],-1,MONTH))

I would also like to show values for the same month 12 months in the past using a simliar measure.  

Here is what my month column looks like. The data type is Date.

Vendor Name	Last Month	Same Month LY
Test Vendor	$45,000 	$30,000 

I have also tried adding relative month to a date table but and updated the measure to the below and all the values were blank.  

TEST Prev Month = CALCULATE(SUM('tbl6PakData_Archive'[Inv6Pak]), 'M1VAULT_STG DATE_DIM'[Relative_Month] = -1)

Any help would be much appreciated!

[Ashish_Mathur]

Hi,

In the M1VAULT_STG DATE_DIM Table, dates should be listed consecutively without any duplicates.  Ensure that in the slicer you select a certain Year and Month (The Year and Month slicer should be built from the M1VAULT_STG DATE_DIM table).  This measure should work fine

TEST Prev Month = CALCULATE(SUM('tbl6PakData_Archive'[Inv6Pak]),previousmonth('M1VAULT_STG DATE_DIM'[Date]))

[Anonymous · Accepted solution]

HI @Nsimpson,

You can use the following measure formula to get the last month and the same period last year amounts:

Prev Month =
VAR currDate =
    MAX ( 'tbl6PakData_Archive'[Month] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
RETURN
    CALCULATE (
        SUM ( 'tbl6PakData_Archive'[Inv6Pak] ),
        FILTER (
            ALLSELECTED ( 'tbl6PakData_Archive' ),
            YEAR ( [Month] ) = YEAR ( prevDate )
                && MONTH ( [Month] ) = MONTH ( prevDate )
        ),
        VALUES ( 'tbl6PakData_Archive'[Vendor Name] )
    )
    
Prev Month LY=
VAR currDate =
    MAX ( 'tbl6PakData_Archive'[Month] )
VAR prevDate =
    DATE ( YEAR ( currDate )-1, MONTH ( currDate ) - 1, DAY ( currDate ) )
RETURN
    CALCULATE (
        SUM ( 'tbl6PakData_Archive'[Inv6Pak] ),
        FILTER (
            ALLSELECTED ( 'tbl6PakData_Archive' ),
            YEAR ( [Month] ) = YEAR ( prevDate )
                && MONTH ( [Month] ) = MONTH ( prevDate )
        ),
        VALUES ( 'tbl6PakData_Archive'[Vendor Name] )
    )

The above formula should work when your use date and 'vendor name' field with the measure formula to create a table visual.

Regards,

Xiaoxin Sheng

[Nsimpson]

Hi Xiaoxin,

Thank you for the help!  When i write this measure and put the vendor name and the measure formula as columns for the Table visual I get this error.  Do you know of a way to get around this?

Below is the measure I wrote which works by itself but not when I add VENALPHA

Prev Month2 = VAR currDate = MAX(tbl6PakData_Archive[Month]) VAR prevDate = DATE (YEAR (currDate), MONTH(currDate) -1, DAY(currDate)) RETURN CALCULATE(SUM('tbl6PakData_Archive'[Inv6Pak]), FILTER(ALLSELECTED('tbl6PakData_Archive'), YEAR([Month]) = YEAR(prevDate) && MONTH([Month]) = MONTH (prevDate)), VALUES('tbl6PakData_Archive'[VENALPHA]))

[Anonymous]

Hi @Nsimpson,

My expression should work with common date values, what type of value are your month fields stored? If they are text or other types of values, please try to convert them to date first.

Regards,

Xiaoxin Sheng

[davehus]

Hi @Nsimpson , Dates not being contigious means that you do not have sequential dates in your date table or the relationship is not correct within the model. Give both a check.