Forum Discussion

Chrism123's avatar
Chrism123
Frequent Visitor
8 years ago
Solved

Last date Previous Month

Hi,   I am trying to find a way to calculate the sum of a value for the last day of the previous month.   For example: If I filter for 31.10.2017 the measure should return the values of 30.09.201...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI Chrism123

     

    May be this one

     

    =
CALCULATE (
    SUM ( Table1[Amount] ),
    LASTDATE ( PREVIOUSMONTH ( Kalender[Date] ) )
)
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Try this

 

=CALCULATE(SUM(Table1[Amount]),PREVIOUSMONTH(Kalender[Date]))

 

Ensure that in the Date filter is created from the Kalendar table.  There should be a relatioship from the Date column of the Table1 to the Date column of the Kalendar table.

  • Chrism123's avatar
    Chrism123
    Frequent Visitor
    8 years ago

    Hi,

     

    thanks for your reply. When I use this formula I get the sum for all the dates of the previous month. But I only want to have it for the last day of the month.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      HI Chrism123

       

      May be this one

       

      =
CALCULATE (
    SUM ( Table1[Amount] ),
    LASTDATE ( PREVIOUSMONTH ( Kalender[Date] ) )
)
      • Chrism123's avatar
        Chrism123
        Frequent Visitor
        8 years ago

        Thanks that works :smileyhappy: 

         

        Do you know if it is also possible to apply this forumula to a measure that is already existing?

         

        I would like to filter the measure [Sales EUR FX] to be calculated for the last date on the previous month. Is this possible?

        Here is my latest approach:

         

        =Filter(LASTDATE(PREVIOUSMONTH(Kalender[Date])),[Sales EUR FX])