Create a calculated Column for is previous month

[Anonymous]

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:

IsCurrentMonth = IF (

YEAR ( DimDate[full date] ) = YEAR ( TODAY () )

&& MONTH ( DimDate[full date] ) = MONTH ( TODAY () );

"Yes";

"No"

)

I want to do this cause I want to compare the results of the previous month to the results of the month before that, any suggestions?

Thanks

[dobregon]

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.

[Anonymous]

@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!

[dobregon]

yes @Anonymous  with both measures you can create the values and other to the alert

[amitchandak]

Try like

IsPreviousMonth =
var _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]

Hi this isn't working as it still says no for dates in january,but maybe I am missing something.

[dobregon]

@Anonymous so, you have some months without rows in your dataset and the query give errors due to it doesnt find the previous month righ?

Your solution in that case is the IFERROR. You can put that if there is an error in the measure give to you a blank or a 0

[amitchandak]

Have you created a date table.  I shared few link in last update

Date = calendar(date(2017,01,01),date(2020,12,31))

OR

Date = calendar(date(2017,01,01),Today())

[Anonymous]

Yep I did, it keeps saying yes for current month,but not for previous month. 

But I already fixed with the formulas u gave me. 

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)))

The calculated column isn´t working tho, but I can move on with my analysis! 

[amitchandak · Accepted solution]

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/

[Anonymous]

@amitchandak  Somehow this isn't working and formulas give me back empty values. My Data is last updated on 31/01/2020 (so no data on feb 2020 & march 2020)

 

If I want expenses of january 2020 I use following formula right (considering today is march 2020)?

Expense 2nd last month = CALCULATE([DumptotalExpense];DATESMTD(dateadd(DimDate[full date];-2;MONTH))) 

And if I want expenses from december 2019 I use following formula right?

Expense 3th last month = CALCULATE([DumptotalExpense];DATESMTD(dateadd(DimDate[full date];-3;MONTH))) 

The higlighted measure looks like this:

DumptotalExpense = sum(Sheet1[Uitgaven])

@amitchandak

[dobregon]

@Anonymous you can't create measure based on (- 3 months, etc) becasue the dates in your database will change, and if today January is -3, in april will be -4 and again.

Depends what you want. If you want to have the sales by month you need to have a measure = sum(sales) and then in the matrixtable you use as rows the month names or whatever hierarchy that you want.

If you want to have in exclusitivy the January sales every time your calculation should be = CALCULATE(SUM(SALES), FILTER(TABLE, TABLE([MONTH]) = 1))

[Anonymous]

@dobregon but in april I would want to analyze the results of february. Cause in april the data of february will be updated.

I would like to create 2 cards:

card one: results of 2 months ago
card two: resultsof 3months ago

And ofcourse if the data is updated more freauentlyI would like to update the cards to; But this isn't the case yet
card one: results this month
card two: resultsprevious month

[dobregon]

@Anonymous so, now as we see you need to have 4 measures 
current month
previous month
2 months ago
3 months ago

And then put in the cards the measures that you want to see. The problem that you will have is if in april you dont have any value in the database, your -2 months will be January (march - 2) instead of February. So, if you need to be dynamic and use a general calendar. You need to create a Calendar table that takes the min date the minimun date in your database and the max date "today". so every refresh of the report it will have your complete calendar and then you cna refer all the calculations of dates to that claendar. Remember to use the iferror to put blanks that solve the majoriry of problems of the table when a value has problem o donest appear 

[Anonymous]

@dobregon and how would I do this? 😄 

[Anonymous]

Could you tell me which formulas calculate what exactly?

MTD Sales = sales current month

Last MTD sales = sales last month?
2nd last MTD sales = sales 2 months ago?
last year MTD (complete) sales = sales current month last year?
last year MTD sales = sales current month last year ?

If I would like to see the results of Jan 2020 I would use this formula right?

ExpenseJaan= CALCULATE([DumptotalExpense];DATESMTD(dateadd(DimDate[full date];-2;MONTH)))

[Tahreem24]

@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.

[Anonymous]

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.

[amitchandak]

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]

Hi thanks for your answer, what is "_date" in this formula?