How to get last value Enter in Previous Month

[IslamAmer]

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 🙂

[Anonymous · Accepted 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. 

[amitchandak]

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

[IslamAmer]

Hi,

Its look has some errors