Previous Months Field

[Anonymous]

Found this thread 

https://community.powerbi.com/t5/Desktop/PREVIOUSMONTH/td-p/13307

tested both these as columns, both worked with correct value
WIP PARTS SUM = CALCULATE(SUM('WebtrimCentral_MTD'[WIP PARTS])) = correct value
Previous Month Date = DATEADD ('WebtrimCentral_MTD'[CreateDate], -1, MONTH) = correct value

Tried to combine to this and it just returns blank?

Prev Month WIP PARTS =
CALCULATE (
SUM('WebtrimCentral_MTD'[WIP PARTS]),
DATEADD ( 'WebtrimCentral_MTD'[CreateDate], -1, MONTH )
)

anybody know more 😞

[jdbuchanan71]

Hello @Anonymous 

Two small changes to your measures.  Your first sum does not need the calculate so go with this.

WIP PARTS SUM = SUM ( 'WebtrimCentral_MTD'[WIP PARTS] )

Your Prev Month amount measure can just use the regular sum measure like so.

Prev Month WIP PARTS =
CALCULATE (
    [WIP PARTS SUM],
    DATEADD ( 'WebtrimCentral_MTD'[CreateDate], -1, MONTH )
)

[Anonymous]

@jdbuchanan71 Thanks that worked. Do i need to use two measures, should I not just combine into one?

Got a new problem now. It works but if I choose to filter based on another field (related) it's showing as blank

[Ashish_Mathur]

Hi,

You may also try this measure

Prev Month WIP PARTS =CALCULATE([WIP PARTS SUM],PREVIOUSMONTH('DateDimension'[Date]))

Hope this helps.

[jdbuchanan71 · Accepted solution]

@Anonymous 

You should have both.  Having the base [WIP PARTS SUM] measure lets you use it other measure like last month, last year, margin rates, etc.  Since you have a date table you should use that as your adjustment for the date calc.

Prev Month WIP PARTS =
CALCULATE (
    [WIP PARTS SUM],
    DATEADD ('DateDimension'[Date], -1, MONTH )
)

Make sure your DateDimension table is marked as the date table.

And turn off the cross filtereing on the relationship.  It should just flow from the DateDimension table to the WebtrimCentral_MTD

And you just make sure to pull any field related to date from your DateDimension table.

Take a look at this training course, they cover a lot of these concepts and it's free.  https://www.sqlbi.com/p/introducing-dax-video-course/