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 All,
I have Table contain a water gague vlaues reading to calculate the consumption need to subtract the last value at Current month with last value in Previous Month
I get the first one but could not get the other "last value in Previous Month "
Consumption = 5 - 2 = 3
value | Date |
1 | 1-7-2020 |
2 | 30-7-2020 |
3 | 1-8-2020 |
5 | 4-8-2020 |
Thanks 🙂
Solved! Go to Solution.
Hi @IslamAmer
I build a table like yours to have a test. And I add a Month column.
Month = MONTH('Table'[Date])
Table:
Then I build a measure to achieve your goal.
Measure 2 =
VAR _a =
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
= MAXX (
FILTER ( ALL ( 'Table' ), 'Table'[Month] = SUM ( 'Table'[Month] ) ),
'Table'[Date]
)
&& 'Table'[Month] = SUM ( 'Table'[Month] )
)
)
VAR _b =
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
= MAXX (
FILTER ( ALL ( 'Table' ), 'Table'[Month] = SUM ( 'Table'[Month] ) - 1 ),
'Table'[Date]
)
&& 'Table'[Month]
= SUM ( 'Table'[Month] ) - 1
)
)
RETURN
_a - _b
Result:
You can download the pbix file from this link: How to get last value Enter in Previous Month
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @IslamAmer
I build a table like yours to have a test. And I add a Month column.
Month = MONTH('Table'[Date])
Table:
Then I build a measure to achieve your goal.
Measure 2 =
VAR _a =
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
= MAXX (
FILTER ( ALL ( 'Table' ), 'Table'[Month] = SUM ( 'Table'[Month] ) ),
'Table'[Date]
)
&& 'Table'[Month] = SUM ( 'Table'[Month] )
)
)
VAR _b =
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
= MAXX (
FILTER ( ALL ( 'Table' ), 'Table'[Month] = SUM ( 'Table'[Month] ) - 1 ),
'Table'[Date]
)
&& 'Table'[Month]
= SUM ( 'Table'[Month] ) - 1
)
)
RETURN
_a - _b
Result:
You can download the pbix file from this link: How to get last value Enter in Previous Month
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@IslamAmer , try with a date table joined to date of your table
This month = CALCULATE(lastnonblankvalue(Table[Date],max(Table[value])),DATESMTD('Date'[Date]))
Last month = CALCULATE(astnonblankvalue(Table[Date],max(Table[value])),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Hi,
Its look has some errors
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |