topic Previous Month without any date filter in DAX Commands and Tips

Previous Month without any date filter

Anonymous — Tue, 05 Oct 2021 23:16:33 GMT

Hi,

i have a table like this

id name country month year sales

2 dfd zy 2 2020 10

What I need to calculate is month and previous month based on selected month and year and the same where I have to calculate which quarter is selected and get previous quarter.

The issue is that I have no date, so I cant use any time intelligence functions either my measure overwrites all the other columns.

How can I calculate previous month or quarter based on the slicer if there is no date table ?

Re: Previous Month without any date filter

VahidDM — Wed, 06 Oct 2021 00:05:26 GMT

Hi Anonymous

It would be better if you add date table to your report. Can you add more details about the result you are looking for.

Try this if you just want to find the Last month number:

Last Month =
Var _Date =
selectedvalue(table[month])-1

and to find the quarter you can use QUARTER dax code, something like this:

Quarter=
Var _D = date(selectedvalue(table[year]),selectedvalue(table[month]),01)
return
QUARTER(_D)

Previous Quarter=
Var _D = date(selectedvalue(table[year]),selectedvalue(table[month]),01)

Var _3D = DATEADD(_D,-3,month)

return
QUARTER(_3D)

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

Appreciate your Kudos!!

Re: Previous Month without any date filter

Anonymous — Wed, 06 Oct 2021 07:13:16 GMT

Hi, im not sure I can fully follow. The problem is that i cant use dateadd as I dont have any date in my table at all. I tried using previous quarter but this says the var _d is not the right one.

What i want is the sum(sales) of the quarter that is selected in the slicer (which is quarter and year) and show previous quarter sales next to it based on which quarter and year is selected. The same for month, if a month and year are selected, to show sum(sales) of that and next to it previous month based on the selection filter

Re: Previous Month without any date filter

Anonymous — Wed, 06 Oct 2021 07:40:24 GMT

I tried sth like SUM ( table[sales] ),

, FILTER ( ALL('table'),'table'[QuarterYear]= MAX('table'[QuarterYear]) - 1 but this doesnt give the right values for previous quarter sales, but not sure why