Referencing previous month data in a calculated column

[RY33]

I have a user table with new set of monthly data added to the table every month.

I have a calculated column that works out user "breach status" based on a few columns in the table  

I am trying to work out how the user "breach status" has changed month on month.  My approach was to add another caluclated column that calculates user "breach status" for previous month, then add another calculated column that compares the two columns.

Problem: 

I cannot figure out how to pick last month data for each user in the row relating to the current month. Hopefully attached screenshot with makes clear what I am trying to do. I need to find a way to do what the yellow cells do in excel.

Please help

[v-gizhi-msft]

Hi,

Please try this measure:

Measure = 
var a = CALCULATE(SUM('Table'[Breach Status]),DATEADD('Table'[Date],-1,MONTH))
return
IF(NOT(ISEMPTY(DATEADD('Table'[Date],-1,MONTH))),SWITCH(SUM('Table'[Breach Status])-a,0,"Same",-1,"Decrease",1,"Increase"))

The result shows:

Hope this helps.

Best Regards,

Giotto Zhi

[RY33]

Hi Giotto,

Thank you for your suggestion.

Could break down (explain) what the second part of your formula is doing after 'return? My table is more complicated than the simplified example I provided and I cannot figure out how to apply your formula as I don't understand it). I tried breaking this into two steps by first creating a measure column instead of the variable  that you suggested. I expected that this will give me a column with the previous month breaches (effectively shifting the 'breaches' one month up, so after that it's just comparing two columns. 

My Column

Breach previous month = CALCULATE(SUM('Table'[Breach Status]),DATEADD('Table'[Date],-1,MONTH))

Code works (no error) but doesn't return any values, which makes me wonder if DATEADD works when my dates are not precisely one month apart. For example DATEADD('Table'[Date],-1,MONTH work when the first data is 01/01/2019 and the second date is 05/02/2019 (in dd/mm/yyyy format) or do they have to be 01/01/2019, 01/02/2019, 01/03/2019,etc

[v-gizhi-msft]

Hi,

Because my sample data's date is from 2019-12 to 2020-2 and there is no data in 2019-11.

So i use NOT and ISEMPTY to check whether the previous month exists.

Then i make current month data minus previous month data to show its status as 'Increase' or 'Decrease'.

Best Regards,

Giotto Zhi

[amitchandak]

@RY33 

The formula you creates is correct. But you need Date calendar. Other wise you will end putting all filter that will remove other filter.

Breach previous month = CALCULATE(SUM('Table'[Breach Status]),DATEADD('Date'[Date],-1,MONTH))

You can also use datemtd and totalmtd 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,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)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

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/

[RY33]

@amitchandak 

I have reviewed the links ou suggested and added a date table. Still the formula below is returning blanks

TEST Breaches_m2 = CALCULATE(SUM('Main Table'[Client Breach]),DATEADD('Date Table'[Date],-1,MONTH))

My dates in the main table are monthly and not always on the same day of the month, for example could be (dd/mm/yyyy)

01/02/2019

03/03/2019

04/04/2019

01/05/2019, etc

So when a formula above is looking at the row with the date 01/05/2019, would it not apply a filter for "01/04/2019" - i.e. same day of the previous month and hence return a blank as there is no such date in my main table? I tried creating a new column with the 1st of each month in both the main and the date table to get around this but still couln't get it working. What am I doing wrong? Thanks

[amitchandak]

Is DD/MM/YYYY is detected as a date. Even if you have data at month, create the date calendar at the day level with all dates.

Have a month start date or end date in the fact.

If required create mm/dd/yyyy column.

[Sabeeluzzama]

Hi RY33,

ALternate solution to resolve this , PLease create a duplicate table of previous month and and add +1 to month to the previous month (now duplicate table will have month same as current month).

Then create the composite in duplicate table by concatinating month,userid.

then join current month and duplicate month table on basis of composite key ,

then you can compare two columns and get the difference.