Sum of previous month value filtered by max/last date in dataset

[Anonymous]

Hi All,

Im trying to get the delta between latest rig count and previous count (MoM change). My data has several dates (Publish Date) to get the right rig count number I need to use only the latest date of each month.

To get the latest date Im using this measure: 

Latest_Rig_Count Nam = CALCULATE(SUM('impt BakerHughesNam'[RigCount]),FILTER('impt BakerHughesNam','impt BakerHughesNam'[PublishDate] = MAX('impt BakerHughesNam'[PublishDate]))) (THIS ONE WORKS FINE)

However, when trying to get the previous count (last month count): it doesnt work.

Previous Count = CALCULATE(sum('impt BakerHughesNam'[RigCount]), LASTDATE (PREVIOUSMONTH('Date Table'[Date])))

Using date table or the publish date. both dont work.

 The data is public at baker hughes website: http://phx.corporate-ir.net/phoenix.zhtml?c=79687&p=irol-reportsother

Im trying to get something similar to this with the KPI ticker with current count, previous count and delta % for year and month.

[Anonymous]

Hi All,

Im trying to get the delta between latest rig count and previous count (MoM change). My data has several dates (Publish Date) to get the right rig count number I need to use only the latest date of each month.

To get the latest date Im using this measure: 

Latest_Rig_Count Nam = CALCULATE(SUM('impt BakerHughesNam'[RigCount]),FILTER('impt BakerHughesNam','impt BakerHughesNam'[PublishDate] = MAX('impt BakerHughesNam'[PublishDate]))) (THIS ONE WORKS FINE)

However, when trying to get the previous count (last month count): it doesnt work.

Previous Count = CALCULATE(sum('impt BakerHughesNam'[RigCount]), LASTDATE (PREVIOUSMONTH('Date Table'[Date])))

Using date table or the publish date. both dont work.

 The data is public at baker hughes website: http://phx.corporate-ir.net/phoenix.zhtml?c=79687&p=irol-reportsother

Im trying to get something similar to this with the KPI ticker with current count, previous count and delta % for year and month.

Any ideas?

Best,

Reyna

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can try to use following measure formula, I add a variable to get previous month max data based on current country group:

Previous Count =
VAR currDate =
    MAX ( 'impt BakerHughesNam'[PublishDate] )
VAR prevDate =
    CALCULATE (
        MAX ( 'impt BakerHughesNam'[PublishDate] ),
        FILTER (
            ALLSELECTED ( 'impt BakerHughesNam' ),
            [PublishDate] < currDate
                && FORMAT ( [PublishDate], "mm/yyyy" )
                    = FORMAT ( DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, 1 ), "mm/yyyy" )
        ),
        VALUES ( 'impt BakerHughesNam'[Country] ),
        VALUES ( 'impt BakerHughesNam'[County] )
    )
RETURN
    CALCULATE (
        SUM ( 'impt BakerHughesNam'[RigCount] ),
        FILTER ( ALLSELECTED ( 'impt BakerHughesNam' ), [PublishDate] = prevDate ),
        VALUES ( 'impt BakerHughesNam'[Country] ),
        VALUES ( 'impt BakerHughesNam'[County] )
    )

If above not help, please share some sample data with expect result for test. 

Regards,

Xiaoxin Sheng

[Anonymous]

Hey !

I used the Add Date  function - 7 days ( as they publish every friday) and it worked!