topic Re: DAX : Sum previous month automatically in Desktop

DAX : Sum previous month automatically

PBIBeginner2022 — Tue, 07 Jun 2022 14:57:27 GMT

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

Re: DAX : Sum previous month automatically

Hariharan_R — Tue, 07 Jun 2022 15:40:05 GMT

Hi,

Try below measure.

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

Re: DAX : Sum previous month automatically

PBIBeginner2022 — Wed, 08 Jun 2022 07:51:20 GMT

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

Re: DAX : Sum previous month automatically

Anonymous — Fri, 10 Jun 2022 08:23:38 GMT

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

Re: DAX : Sum previous month automatically

PBIBeginner2022 — Mon, 13 Jun 2022 09:13:43 GMT

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

Re: DAX : Sum previous month automatically

Anonymous — Mon, 13 Jun 2022 10:04:35 GMT

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

Re: DAX : Sum previous month automatically

PBIBeginner2022 — Mon, 13 Jun 2022 13:16:49 GMT

Thanks for your answer Anonymous 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,

Re: DAX : Sum previous month automatically

Anonymous — Tue, 14 Jun 2022 01:52:51 GMT

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

Re: DAX : Sum previous month automatically

PBIBeginner2022 — Tue, 14 Jun 2022 07:06:43 GMT

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

Re: DAX : Sum previous month automatically

Anonymous — Tue, 14 Jun 2022 08:02:50 GMT

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

Re: DAX : Sum previous month automatically

PBIBeginner2022 — Tue, 14 Jun 2022 08:40:32 GMT

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

Re: DAX : Sum previous month automatically

Anonymous — Tue, 14 Jun 2022 08:48:42 GMT

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

Re: DAX : Sum previous month automatically

bettyfish — Mon, 02 Sep 2024 22:11:10 GMT

when i try this the very first month, instead of being blank, includes the total of all rows

beg bal BS = var result = calculate([FTP-TTD],PREVIOUSMONTH('tbl_Calendar'[Date]))

would you have any idea what i can be doing wrong??

thanks!

betty

Re: DAX : Sum previous month automatically

Ashish_Mathur — Tue, 03 Sep 2024 02:44:53 GMT

Hi,

Share the download link of the PBI file and show the problem there very clearly.