cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
reynags Frequent Visitor
Frequent Visitor

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

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.DataBaker.PNGBaker2.PNG
 
 
 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.
 
Baker3.PNG
 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @reynags ,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
3 REPLIES 3
reynags Frequent Visitor
Frequent Visitor

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

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.DataBaker.PNGBaker2.PNG
 
 
 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.
 
Baker3.PNG
 
 
Any ideas?
 
Best,
 
Reyna
Community Support Team
Community Support Team

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

Hi @reynags ,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
reynags Frequent Visitor
Frequent Visitor

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

Hey !

 

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