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
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!