Forum Discussion
Create a calculated Column for is previous month
Hi all,
I would like to create a calculated column that shows me if that date is in the previous month yes or no. How should I do this?
I already have a calculated column for is current month and I would like to have a similar one but then for is previous month. And I used this DAX code to do this:
Thanks
For data you can use datesmtd and totalmtd with date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) 2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
19 Replies
IsPreviousMonth =
_date = date(year(TODAY () ),MONTH ( TODAY ()-1 ),day(TODAY ()))
return
IF (
YEAR ( DimDate[full date] ) = YEAR (_date )
&& MONTH ( DimDate[full date] ) = MONTH ( _date );
"Yes";
"No"
)Anonymous ,
You can use Previous month or Year DAX to get the expected result.
IsPrevoiusMonth = IF (YEAR ( DimDate[full date] ) = YEAR(Today())&& MONTH ( DimDate[full date] ) = PREVIOUSMONTH(DimDate[full date]);"Yes";"No")Let know if this solves your problem else you can use DATESADD DAX also.Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.Hi Tahreem24 thanks for your answer. This created an error: A table of multiple values was supplied where a single value was expected.
When using Dateadd like this:IsPreviousMonth = IF (YEAR ( DimDate[full date] ) = YEAR(Today())&& MONTH ( DimDate[full date] ) = DATEADD(DimDate[full date];-1;MONTH);"Yes";"No")
--> It didn't create an error but it didn't give the right result.
i think you are doing many many many difficult things for something that maybe it is very simple. example:
Imagine that you have a table with sales values by date or month or whatever.1. you want to know the sum of Sales in the matrix table for the period selected or viewed in the matrix, you will do something like this:
Current Sales =CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]))2. You can create similar measure from the previous month
Currante Sales PrevM=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey]))
With both measures you can have matrix table, line charts very beautifull and this second measure give to you the idea that you can have measures for the previous year too but the same month.
And if what you want it is the column calculated you can create the column calculated using the same code.dobregon thanks for your answer.
The thing is I want to have a card with total expenses previous month and a card with total expenses the month before that and then send alerts when one exceeds the other ( or maybe even a card with expenses this month and expenseslast month but that depends on the datarefresh frequency). Would this also be possible with measures?
thanks!yes Anonymous with both measures you can create the values and other to the alert
