Forum Discussion

Delphia's avatar
Delphia
Icon for Advocate II rankAdvocate II
5 years ago
Solved

FILTER by previous available date

Hi dear community,

I need your help with filtering data. I need to create a measure that will calculate total amount for the previous date in table.

I have the following measure:

Totals Yesterday = CALCULATE(
                                                  SUM(table[sale]),
                                                  FILTER(
                                                             'Date', 'Date'[Date] = TODAY () - 1
                                                   )
                                )
 
It works well. The problem appears when I don't have values for Yesterday but the last values were added several days ago.
In this case I want to show data for the previous available date in table. 
 
 
I would appreciate your help!
Need Help
  • Delphia's avatar
    Delphia
    5 years ago

    Solved with the following measure, proposed by Anonymous in another topic

    Previous Value  =
    VAR CurrentDate = MAX(cleaned_row_count[Date])
    VAR ClientID = MAX('table'[id])
    VAR PreviousDate = CALCULATE(MAX(cleaned_row_count[Date]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]<CurrentDate))
    VAR Result = CALCULATE(SUM(cleaned_row_count[Number of Rows]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]=PreviousDate))
    RETURN Result

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Delphia , With help from date table

     

    max date

    Measure =
    var _max = maxx(allselected('Date'), Date[Date])
    return
    CALCULATE([sales],filter('Date', 'Date'[Date] =_max))

     

     

     

    Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

     

    Day Intelligence - Last day, last non continous day
    https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

    • Delphia's avatar
      Delphia
      Icon for Advocate II rankAdvocate II
      5 years ago

      Thank you so much for your answer.

      It doesn't work for me. Let me precise a little bit my question. My scheme looks like: 

      Above in my question I replaced "Number of Rows" by "Sale", sorry.

      I created a measure using your patern and get the following:

       
      Last Day Non Continuous =
                                               CALCULATE(
                                                        SUM(cleaned_row_count[Number of Rows]),
                                                        FILTER(
                                                                  ALLSELECTED('Date'),
                                                                  'Date'[Date] = MAXX(
                                                                                FILTER(
                                                                                ALLSELECTED('Date'),
                                                                               'Date'[Date] < MAX('Date'[Date])
                                                                                ),
                                                                  'Date'[Date]
                                                                  )
                                                        )
                                             )
      Nevertheless, my table shows empty values for Last Day Non Continuous.
       
      For Total Rows Today column I use the following measure:
      Total Rows Today =
      var total = CALCULATE(sum(cleaned_row_count[Number of Rows]),
                  FILTER(cleaned_row_count, cleaned_row_count[Date]= TODAY()))
      return
      IF(ISBLANK(total), 0, total)
       
      Date table is autogenerated: 
      Date = CALENDARAUTO()
      So maximum value is the end of this year: 2021-12-31
       
      Thank you in advance for your help! 
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Delphia,

        Can you please share a sample pbix file with some dummy data(keep the raw tale schema) and the expected results? It should help us clarify your scenario and test to coding formula.

        How to Get Your Question Answered Quickly  

        Notice: please not add sensitive/real data in it.

        Regards,

        Xiaoxin Sheng

  • Delphia's avatar
    Delphia
    Icon for Advocate II rankAdvocate II
    5 years ago

    Solved with the following measure, proposed by Anonymous in another topic

    Previous Value  =
    VAR CurrentDate = MAX(cleaned_row_count[Date])
    VAR ClientID = MAX('table'[id])
    VAR PreviousDate = CALCULATE(MAX(cleaned_row_count[Date]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]<CurrentDate))
    VAR Result = CALCULATE(SUM(cleaned_row_count[Number of Rows]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]=PreviousDate))
    RETURN Result