topic Re: calculate previous month based on selected month in DAX in DAX Commands and Tips

calculate previous month based on selected month in DAX

Anonymous — Wed, 15 Dec 2021 10:49:48 GMT

Hi All,

How can i get previous month data based on selected month in DAX query?

tower value = feb 2020 data.
tower previous = previous month data (jan 2020).

For example i selected feb 2020 and i want jan 2020 data also but cant figure out how to calculate in DAX.
Here my table for reference.

Re: calculate previous month based on selected month in DAX

DimaMD — Wed, 15 Dec 2021 11:20:07 GMT

HI, Anonymous
Try this measure

Measure = CALCULATE( SUM('Table'[Value]),PREVIOUSMONTH('Dates'[Date] ))

You also need a Calendar Table

Re: calculate previous month based on selected month in DAX

Anonymous — Thu, 16 Dec 2021 03:21:09 GMT

Hi DimaMD ,

i try that measure but the data dont show up

thanks.

Re: calculate previous month based on selected month in DAX

Anonymous — Mon, 20 Dec 2021 07:55:48 GMT

Hi Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Month_m = MONTH('Table'[Month])

MonthYear = FORMAT('Table'[Month],"mmm")&" "&RIGHT('Table'[Month],2)

2. Create measure.

Tower Previous = VAR _MONTH=SELECTEDVALUE('Table'[MonthYear]) VAR _Attribute=SELECTEDVALUE('Table'[Attrubute]) var _last=MAXX(FILTER(ALL('Table'),'Table'[MonthYear]=_MONTH),'Table'[Month_m]) -1 return CALCULATE(SUM('Table'[Tower Value]),FILTER(ALL('Table'), 'Table'[Month_m]=_last&&'Table'[Attrubute]=_Attribute&&'Table'[Category]=MAX('Table'[Category])))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: calculate previous month based on selected month in DAX

DimaMD — Mon, 20 Dec 2021 13:37:48 GMT

Anonymous Hi
Do you have a relationship between the date table and the data table?

Re: calculate previous month based on selected month in DAX

Anonymous — Fri, 07 Jan 2022 02:11:48 GMT

yes both table have relationship

Re: calculate previous month based on selected month in DAX

Anonymous — Tue, 11 Jan 2022 04:16:49 GMT

Hi Anonymous ,
thanks it work but when i put 2021 data it doesn't appear.
can u advice me what i need to do?