Forum Discussion

sergiod04's avatar
sergiod04
Frequent Visitor
6 years ago
Solved

ENDOFMONTH + Most Recent Date

Hello, 

 

I have a table which brings back values for the last date of the month and the most recent date available in the data set. For example, if the most recent date in the data set is 8/10/2020, it will bring back: 

                         06/30/2020         07/31/2020         8/10/2020 

Dimension 1          345                     450                    465

Dimension 2         1,350                   756                    820

 

im doing it with the following formula: 

Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = ENDOFMONTH(DATE_TABLE[Date])))
 
The problem is that if i select 8/8/2020 as a filter for my max date (not the most recent date in my calendar table), i get this: 
 

                         06/30/2020         07/31/2020       

Dimension 1          345                     450                   

Dimension 2         1,350                   756               

 

where the desired product would be this: 

 

                         06/30/2020         07/31/2020         8/8/2020 

Dimension 1          345                     450                    365

Dimension 2         1,350                   756                    210

 

help?

  

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    sergiod04  Try like

    Measure = CALCULATE (lastnonblankvalue(DATE_TABLE[Date],SUM(VALUE))), based on grouping it will choose date. Take month year on axis/row/column

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    sergiod04 , try like

    Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = max(DATE_TABLE[Date])))

    or
    Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = lastdate(DATE_TABLE[Date])))

    • sergiod04's avatar
      sergiod04
      Frequent Visitor
      6 years ago

      Hi amitchandak , 

       

      Thanks for responding. With both the MAX and LASTDATE, it is returning all dates, not just the last day of the month + the most recent one. 😕 

       

      end of month was doing the trick of returning only the last day of the month + the last date when i didnt have any date filters applied. the problem is when i apply a date filter that does not land on an end of month. 

       

      i appreciate your help. 

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        sergiod04  Try like

        Measure = CALCULATE (lastnonblankvalue(DATE_TABLE[Date],SUM(VALUE))), based on grouping it will choose date. Take month year on axis/row/column