Forum Discussion

RY33's avatar
RY33
Frequent Visitor
6 years ago

Referencing previous month data in a calculated column

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 ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
RY33
Frequent Visitor
6 years ago
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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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.