topic Re: Getting last month sales without any date table in DAX Commands and Tips

Getting last month sales without any date table

Anonymous — Wed, 27 Oct 2021 21:36:34 GMT

Hi all,

I cant use a date table for various reasons but I cant make it work how to get previous month sales without the date time intelligence.

What i want is when the user selects 2019 and month 1 he sees the sales of 2018 and month 12 next to it.

Whats the replacement for Calculate(SUM(sales), dateadd(date, month, -1) if the date table isnt there?

I have only one table with my sales and companies.

I tried using calculate(SUM(table (sales), filter(table(monthyear)= selectedvalue(monthyear) -1 but this doesnt show me nothing , just 0s. Any ideas?

Thanks!

Re: Getting last month sales without any date table

AlexisOlson — Wed, 27 Oct 2021 21:54:47 GMT

You need something to remove or replace the current month filter context.

Try something this:

PrevMonthSum = VAR SelectedMonthYear = SELECTEDVALUE ( table1[monthyear] ) RETURN CALCULATE ( SUM ( table1[sales] ), table1[monthyear] = SelectedMonthYear - 1 )

or this

SumPrevMonth = CALCULATE ( SUM ( table1[sales] ), FILTER ( ALL ( table1[monthyear] ), table1[monthyear] = SELECTEDVALUE ( table1[monthyear] ) - 1 ) )

Re: Getting last month sales without any date table

Jihwan_Kim — Thu, 28 Oct 2021 03:30:57 GMT

Hi,

If you have a column for soring a monthyear column, it will be much easier to author DAX measure.

I assume you do not have a column for sorting a monthyear column, and monthyear column is a text type.

In that case, please check the below.

Sales total : =

SUM('Table'[sales] )

Previous month sales : =

VAR yearnumber =

INT ( RIGHT ( SELECTEDVALUE ( 'Table'[monthyear] ), 4 ) )

VAR monthnumber =

INT ( LEFT ( SELECTEDVALUE ( 'Table'[monthyear] ), 2 ) )

VAR currentyearmonthnumber = yearnumber * 12 + monthnumber

RETURN

CALCULATE (

[Sales total :],

FILTER (

ALL ( 'Table' ),

CALCULATE (

INT ( RIGHT ( SELECTEDVALUE ( 'Table'[monthyear] ), 4 ) ) * 12

+ INT ( LEFT ( SELECTEDVALUE ( 'Table'[monthyear] ), 2 ) ) = currentyearmonthnumber - 1

)

Re: Getting last month sales without any date table

Anonymous — Thu, 28 Oct 2021 08:45:15 GMT

Thanks to both of you! I tried both but the problem was that the first solution Jihwan_Kim doesnt give me any values, I guess its because of the ALL (table) as I have to show also other entities in the table. I tried to remove that but that didnt do anything either. I only get blanks.

AlexisOlson When I try your measure it gives me values but when I select month beginning like 2020 and month 1, it doesnt show the previous year month value ( it should show month 12 and year 2019) , it just shows blank. I have a filter on the page where I have monthyear when the measure works but as the user wants the date slicer in the format of Jan-2020 , then below measuer wont show me anything although the Jan-2020 is sorted by monthyear. Not sure how to resolve this?

Re: Getting last month sales without any date table

Jihwan_Kim — Thu, 28 Oct 2021 12:24:49 GMT

Hi,

Thank you for your feedback.

I think the reason that my solution gives a result in my model and it does not give any result in your model is because my model and yours are different.

Without knowing your model, I can only rely on my imagination and I have to create something new.

Re: Getting last month sales without any date table

Anonymous — Mon, 01 Nov 2021 13:21:33 GMT

HI Jihwan_Kim ,

I just added some dummy data where there is a date slicer int he format of aug 2021 etc and I tried your measures , but it doesnt give me any values? What am I doing wrong?

I just have the file here ; https://github.com/userdata21/test/blob/main/previous%20month.pbix

Many thanks!

Re: Getting last month sales without any date table

Jihwan_Kim — Mon, 01 Nov 2021 14:12:15 GMT

Hi,

Thank you for your message.

Please try to fix the measure like below.

I think RIGHT function and LEFT function are missplaced.

Mine is starting with month number, but yours is starting with year number.

Previous month sales : =
VAR yearnumber =
INT ( LEFT ( SELECTEDVALUE ( Data[Sort month year] ), 4 ) )
VAR monthnumber =
INT ( RIGHT ( SELECTEDVALUE ( 'Data'[Sort month year] ), 2 ) )
VAR currentyearmonthnumber = yearnumber * 12 + monthnumber
RETURN
CALCULATE (
[Amount Sum],
FILTER (
ALL ( Data ),
CALCULATE (
INT ( LEFT ( SELECTEDVALUE ( Data[Sort month year] ), 4 ) ) * 12
+ INT ( RIGHT ( SELECTEDVALUE ( Data[Sort month year] ), 2 ) )
) = currentyearmonthnumber - 1
)
)

Re: Getting last month sales without any date table

AlexisOlson — Mon, 01 Nov 2021 14:50:20 GMT

What does your [monthyear] column look like? If it's an integer like 201912, then subtracting 1 from 202001 definitely doesn't give the right result. I'd suggest working with a column that's the last day of the month so you can use EOMONTH to shift dates. You might need to create a new calculated column for this but I can't tell you quite how to write one without knowing what [monthyear] looks like.

Re: Getting last month sales without any date table

AlexisOlson — Mon, 01 Nov 2021 14:58:59 GMT

Having looked at your file, here's what I recommend.

Define a new calculated column:

EoM = EOMONTH ( Data[Date], 0 )

Then a measure:

PrevMonthSum = VAR PrevMonthEnd = EOMONTH ( SELECTEDVALUE ( Data[EoM] ), -1 ) RETURN CALCULATE ( SUM ( Data[Amount] ), ALLSELECTED ( Data ), Data[EoM] = PrevMonthEnd )