Previous Month of Measure Relative to Current Filtered Month

[Anonymous]

Hi all, 

I'm trying to create a measure which will return the previous month of a number of other measures relative to the current period from my dimDate table. 

So for example - return Feb-17 when the dimDate is filtered for Mar-17. Is this possible within a switch function or is there a better way to achieve this?

Thanks

[vanessafvg]

@Anonymous something i just leartn this weekend , incase you have any issues if you have a date dimension, make sure that its linked on a date datatype field (not an int as is often the case in date dimensions when one links on the smart key ie 20010101).  this is if you are using using date functions

[vanessafvg · Accepted solution]

you could use dateadd, create a measure and

ie dateadd(date[date],-1, month)

[Anonymous]

But surely that wouldn't work dynamically? It would only work for the previous month relative to now?

[vanessafvg]

@Anonymous true, probably dont quite understand what you asking

probably best to actually demonstrate what you looking for with examples

[Anonymous]

I said in the original post... 

So for example - return Feb-17 when the dimDate is filtered for Mar-17.

[vanessafvg]

return the value of a  measure  or the date?  not 100% clear in my personal opinion

but simple to use a filter with dateadd

ie. calculate(measure, filter(date, date[date] = dateadd(date[date],-1, month)

or  better 

 calculate(measure, previousmonth(date[date])

[Anonymous]

I have a period slicer on my report which comes from my date dimension in the format mmm-yy. This is related to my sales fact table. I want to return the previous month's sales relative to that current filter context. 

So if I select Apr-17 I want it to return sales for Mar-17. If I select Jan-17 I want it to return the sales for Dec-2016.

[Anonymous]

Hi @Anonymous @vanessafvg

What vanessafvg wrote in the first place will accommodate what you are asking. Do you want it to be displayed in a table or in a card-visualization (or something similar)?

Summarizing what will work:

Total sales = SUM(Sales)

Sales last month = CALCULATE([Total sales];DATEADD(TimeDim[Date];-1;MONTH))

If you don't think this will work dynamically, can you please elaborate? I can make this work the way you want you it to.

Best

Martin

[vanessafvg]

@Anonymous

and in terms of the visual are you placing the date on the visual or just the value?  

[Anonymous]

Just the value, within a table.