Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Get value from previous non-null row

I'm comparing daily sales appointments to the prior business day, but I don't know what formula would calculate that prior value for comparison.

 

Here, I'm showing the number of appointments (Measure: NumApp) per appointment date, using DISTINCTCOUNT of id's from a fact table.

 

Using PREVIOUSDAY, I can calculate the previous calendar date for each appointment date. However I have two problems:

  1. I want to compare each day with the previous working day, ie. the previous row with a non-null value
  2. I don't know how to get values for previous non-null rows

The solution would have a new column for previous day's appointments. This would show, for example, 38 as the result for 12 May 2020, and 47 as the result for 11 May.

 

Hope I've described it clearly!

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    I made a simple dable as below:

    First create a calculated column to check which rows are working days:

     

    isworkingday = IF(WEEKDAY('Table'[Date])<6,1,0)

     

    Then create a measure to get the last non-null row which is in working day row.

     

    previous non-null row = 
var _date=MAXX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Numapp]<>BLANK()&&'Table'[isworkingday]=1),'Table'[Date])
Return
CALCULATE('Table'[Numapp],'Table'[Date]=_date)

     

    And you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try first 1 assuming Transaction table have data only for work day

    Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
    Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak - thanks for the quick response!

       

      Actually I can't assume the days for comparison are simply Mondays to Fridays, as there are public holidays. I need to compare each row with the previous date for which there is a non-null value.

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        The assumption the date on which sales is not there is not present in the table. So that we can find that date < the current date and get the sales for that date

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

    Hi Anonymous ，

     

    I made a simple dable as below:

    First create a calculated column to check which rows are working days:

     

    isworkingday = IF(WEEKDAY('Table'[Date])<6,1,0)

     

    Then create a measure to get the last non-null row which is in working day row.

     

    previous non-null row = 
var _date=MAXX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Numapp]<>BLANK()&&'Table'[isworkingday]=1),'Table'[Date])
Return
CALCULATE('Table'[Numapp],'Table'[Date]=_date)

     

    And you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!