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
v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

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's avatar
RY33
Frequent Visitor
6 years ago

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

  • amitchandak's avatar
    amitchandak
    Super 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
        Super 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.

         

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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