Last month total for a category (PREVIOUSMONTH does not work)

[cherrypbi]

Hello,

I'm trying to get last month's total (which is December 2022) for Category = Apple with "PREVIOUSMONTH" function but it does not work. 

Current formula that doesnt work: 

CALCULATE ( SUM ('RevenueTable'[Revenue] ) , FILTER ( 'RevenueTable' , PREVIOUSMONTH('RevenueTable'[Billing Month])) , FILTER ( 'RevenueTable' , 'RevenueTable'[product] = "Apple" ) )

This returns 129, instead of 107 (value I want), 

My formula adds everything in the table except 7/1/2022 which got omitted for some reason...any help is appreciated

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Billing Month column to the Date column of the Calendar Table.  To your visual, drag year and month from the Calendar Table and Category from the RevenueTable.  Write these measures

Rev = SUM ('RevenueTable'[Revenue] )

Rev in previous month = calculate([Rev],previousmonth(Calendar[date]))

Hope this helps.

[v-binbinyu-msft · Accepted solution]

Hi @cherrypbi ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR cur_date =
    TODAY ()
VAR _total =
    CALCULATE (
        SUM ( 'Table'[Revenue] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = "Apple"
                && DATEDIFF ( 'Table'[BillingMonth], TODAY (), MONTH ) = 1
        )
    )
RETURN
    _total

2.  add a card visual with measure 

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SivaMani]

@cherrypbi, try this

Revenue PM = CALCULATE(
    SUM('RevenueTable'[Revenue]),
    'RevenueTable'[Category] = "Apple",
    PREVIOUSMONTH(LASTDATE('RevenueTable'[BillingMonth])),
    All('RevenueTable'[BillingMonth])
    )