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