Previous Month

[uthall]

Hello,

I have a table with data and dates.

I simply want to show a card of the last 1 months data.

What is the DAX syntax for this?

Thanks

Jeff

[v-caliao-msft · Accepted solution]

Hi @uthall,

Do you need aggregate value or detail value? If you need aggregate value for pervious month, you can use PREVIOUSMONTH to achieve this requirement, here is the sample DAX for you reference.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey])) 
https://msdn.microsoft.com/en-us/library/ee634758.aspx?f=255&MSPPError=-2147217396

Regards,

Charlie Liao

[Greg_Deckler]

Typically, you would create a column like so:

Column = MONTH(TODAY()) - MONTH(Table[Date])

And then just filter to 1 or 2 depending upon your definition of "last".

[uthall]

Thanks,

What if TODAY() is Jan 2017, making the previous month DEC 2016?

The return value will be 1-12 = 11 (seems to be an absolute value)

This is also the case if i want Next Month, which is tghe reverse of the above

Any further ideas?

[aaronseaman]

I have just resolved this with the following formula.

PrevMonth = if(Month(today()) = 1, if(year(GP[Month]) & month(GP[Month]) = year(today())-1 & month(today())+11, 1, 0), if(year(GP[Month]) & month(GP[Month]) = Year(today()) & (Month(today()-1)), 1, 0)) 

[tjd]

Thanks for the equation, but you need an extra set of parentheses around MONTH, YEAR, etc. wherever you are doing your math, such as "(YEAR(TODAY())-1) & (MONTH(TODAY())+11)" and "& (Month(TODAY())-1)".  If not, it will not compute correctly.

[aaronseaman]

G'day, 

I have used the following formula in Excel to identify the previous month,  it works also for January.

=IF(MONTH(TODAY())=MONTH(1),IF(AND(MONTH(C13)=MONTH(TODAY())+11,YEAR(C13)=YEAR(TODAY())-1),1,0),IF(AND(MONTH(C13)=MONTH(TODAY())-1,YEAR(C13)=YEAR(TODAY())),1,0))

This formula, however, is not working in Power BI, if anyone can enlighten me to why i would appreciate it.