Calculate Previous month giving blank values

[chavanr]

Hi there,

I am trying to calculate a column for previous month value & it is returning blank value

Recycling Tonnes LM = CALCULATE(SUM('Collection Transactions New'[Recycling Tonnes New]),DATEADD('Calendar'[DWHDimDateKey],-1,MONTH))

Many thanks

[v-yalanwu-msft · Accepted solution]

Hi @chavanr  , 

You could create a column by the following formula:

Recycling Tonnes LM =
VAR _a =
    EOMONTH ( 'Collection Transactions New'[DWHDimDateKey], -1 )
RETURN
    CALCULATE (
        SUM ( [Recycling Tonnes New] ),
        FILTER ( 'Collection Transactions New', EOMONTH ( [DWHDimDateKey], 0 ) = _a ))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[themistoklis]

Hello @chavanr ,

The formula in the image is different from the one on the text message.

First make sure the field  'Calendar'[DWHDimDateKey]  is on date format

Try the following:

Recycling Tonnes LM  =
CALCULATE (
SUM('Collection Transactions New'[Recycling Tonnes New]),
PREVIOUSMONTH ( 'Calendar'[DWHDimDateKey] )
)

Remove .month at the end of the formula

[chavanr]

It is still giving blank values,DWHDimDateKey is marked as date table.