Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
HI @Chrism123
May be this one
= CALCULATE ( SUM ( Table1[Amount] ), LASTDATE ( PREVIOUSMONTH ( Kalender[Date] ) ) )
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.
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.
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.
HI @Chrism123
May be this one
= CALCULATE ( SUM ( Table1[Amount] ), LASTDATE ( PREVIOUSMONTH ( Kalender[Date] ) ) )
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])
Hi @Chrism123
I think this should work
CALCULATE ( [Sales EUR FX], LASTDATE ( PREVIOUSMONTH ( Kalender[Date] ) ) )
very good, thanks. I didn't know that it was possible to use calculate like this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |