DAX : Sum previous month automatically

[PBIBeginner2022]

Hello,

I would like to translate this into DAX language:

I have a column calculated with the value of each item sold and a column with the date of sale of each item.
I would like to create a measure that gives me the total value sold for the month before last. So the current month minus -1.

For example, we are in June, I would like to know the total value sold in May. When we are in July, I would like my measure to automatically calculate the total value sold in June.

Thanks in advance

[v-jayw-msft]

Hi @PBIBeginner2022 ,

Please try these formulas.

Column:

Pre_sales =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[date] = EDATE ( EARLIER ( 'Table'[date] ), -1 )
    ),
    'Table'[value]
)

Measure:

Pre_sales =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[date] = EDATE ( SELECTEDVALUE ( 'Table'[date] ), -1 )
    ),
    'Table'[value]
)

If it doesn't work, please share some sample data and expected result so that we could test the formula.

Best Regards,

Jay 

[PBIBeginner2022]

Hi,

I tried your measurement, but I still have the same problem, no value is displayed. Here is a data set:

I want the sum of the actual month -1. So we are in June, I want the sum of price in May.

Article	Price	Date
ART0001	43	11/06/2022
ART0002	235	19/05/2022
ART0003	43	12/06/2022
ART0004	987	23/05/2022
ART0005	4	13/06/2022
ART0006	54	02/06/2022
ART0007	7	11/08/2022
ART0008	65342	08/06/2022
ART0009	87	13/05/2022
ART0010	342	09/05/2022
ART0011	0	07/06/2022
ART0012	7	28/05/2022
ART0013	34	30/04/2022
ART0014	65	18/05/2022
ART0015	32	13/07/2022
ART0016	9	14/05/2022
ART0017	2	10/08/2022
ART0018	43	05/06/2022
ART0019	235	19/04/2022
ART0020	43	25/08/2022
ART0021	987	11/05/2022
ART0022	4	12/07/2022
ART0023	54	10/05/2022
ART0024	7	24/08/2022
ART0025	65342	30/06/2022
ART0026	87	06/05/2022
ART0027	342	24/04/2022
ART0028	0	21/06/2022
ART0029	7	04/05/2022
ART0030	34	05/05/2022
ART0031	65	17/05/2022
ART0032	96	05/07/2022
ART0033	127	26/05/2022
ART0034	158	25/08/2022
ART0035	189	31/07/2022
ART0036	220	26/08/2022
ART0037	251	03/07/2022
ART0038	282	19/07/2022
ART0039	313	18/05/2022
ART0040	344	14/06/2022

[v-jayw-msft]

Hi @PBIBeginner2022 ,

Thank you for the data.
Please try this formula:

Pre_sales =
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date].[MonthNo]
            = SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) - 1
    )
)

Best Regards,

Jay

[PBIBeginner2022]

Thanks for your answer @v-jayw-msft  but I can't write in my measure 'TABLE'[date].[MonthNo]. I can write 'TABLE'[date] but I can't add .[MonthNo] in my measure. 

Are they other possibilities to write this measure please ?

Best regards,

[v-jayw-msft]

Hi @PBIBeginner2022 ,

What's the format of the date column?

Make sure it's date format otherwise you will need to create another Month column.

Pre_sales = 
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        MONTH ( 'Table'[Date] )
            = MONTH ( MAX ( 'Table'[Date] ) ) - 1
    )
)

Best Regards,

Jay

[PBIBeginner2022]

@v-jayw-msft my problem isn't resolved. I want use this visual to see only the sum of my month minus one sale.

However when I take the column of all my sales of the year into this visual and I filter with column date then relative date then "is in the last month", I have the value that I search. But I need it with a measure to automate this.

[v-jayw-msft]

Hi @PBIBeginner2022 ,

So you want a card visual to dynamically display the previous month's value according to current month?

Measure = CALCULATE(SUM('Table'[Price]),FILTER(ALLSELECTED('Table'),MONTH('Table'[Date]) = MONTH(TODAY())-1))

Best Regards,

Jay

[PBIBeginner2022]

@v-jayw-msft I don't have the same value between my display card and the month of may. I think it is missing something in the measure. I think in the measure we filter on all the month of may of every years. So I think we need to add something filter on the month -1 of the current year and not of all the year. I don't know if your understand me. In reality I have more than 50 years in my column and I want only the current month minus one of the current year.

Please, could you help me for this.

[v-jayw-msft · Accepted solution]

Hi @PBIBeginner2022 ，

This formula should work.

Pre_sales =
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        FORMAT ( 'Table'[Date], "YYYYMM" ) = FORMAT ( EDATE ( TODAY (), -1 ), "YYYYMM" )
    )
)

Best Regards,

Jay

[Hariharan_R]

Hi,

Try below measure.

Previous Month Sales = CALCULATE(SUM(Sales[Sales]),PREVIOUSMONTH('Date'[Date]))

[PBIBeginner2022]

Hi @Hariharan_R 

When I try this, it's wrote "vide" in French (void I Think).

CALCULATE(SUM(......) is good but after to select only the month minus  1 it's complicate because I have date with this format "01/04/2022" for exemple between  year 1988 and 2024 ....