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...
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
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! 
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

Delphia 

This will work:

Previous Date rows =
VAR PrevDate =
    MAXX (
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] < MAX ( 'Date'[Date] )
                && NOT ( ISBLANK ( [Number of rows] ) )
        ),
        'Date'[Date]
    )
RETURN
    IF (
        ISBLANK ( [Number of rows] ),
        BLANK (),
        CALCULATE (
            [Number of rows],
            FILTER ( ALL ( 'Date' ), 'Date'[Date] = PrevDate )
        )
    )
    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Delphia 

      [Number of rows] is a measure:

      Number of rows =SUM(cleaned_row_count[Number of Rows])

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

        Thank you, Paul. It works as a measure, nevertheless, it shows todays values, not for the previous date. Please have a look at my screenshot:

         

        Thank you one more time!