Forum Discussion
Previous Month
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
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=-2147217396Regards,
Charlie Liao
6 Replies
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".
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?
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.
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=-2147217396Regards,
Charlie Liao
