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.2017.

 

This is my latest approch, but it doesn't work:

=CALCULATE(sum(table1),USERELATIONSHIP(Kalender[Date,BatchDate),all(Kalender[Date]),Kalender[Date]=EOMONTH(Kalender[Date],-1))

 

 

 

Hope somebody can help me. Thanks.

 

Christoph

Need Help

7 Replies

  • 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] ) )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi all,

     

    I need help to replicate the below excel formula in BI.

     

    IF($B$1>=Holidays!$E$1,IF(AE2<=EOMONTH($B$1,0),EOMONTH($B$1,1),AE2),IF(AE2<=EOMONTH($B$1,0),EOMONTH($B$1,0),AE2))

     

    I have created a custom column that works out the penultimate working day this month "Penultimate working day" which replicates "Holidays!$E$1" from excel if that makes sense.

     

    $B$1 = "Last Saturday" column
    Holidays!$E$1 = "(Penultimate Day of Month)"

    AE2 = "(FC Completion)"

     

    So in excel it should look like:

     

    IF(Last Saturday>=(Penultimate Day of Month),IF((FC Completion)<=EOMONTH(Last Saturday,0),EOMONTH(Last Saturday,1), (FC Completion)),IF((FC Completion)<=EOMONTH(Last Saturday,0),EOMONTH(Last Saturday,0),(FC Completion)))

     

    I hope that makes sense.