Last date Previous Month

[Chrism123]

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

[Anonymous]

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.

[Ashish_Mathur]

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]

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 · Accepted solution]

HI @Chrism123

May be this one

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

[Chrism123]

Thanks that works  

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])

[Zubair_Muhammad]

Hi @Chrism123

I think this should work

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

[Chrism123]

very good, thanks. I didn't know that it was possible to use calculate like this.