cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
abisaileon11 Regular Visitor
Regular Visitor

Measure that shows the sum of units sold of month selected -1

Hello,

I need to create a measure to show the sum of units sold of the previous month selected,

For example if I selected September I need to see the amount of sold units in august

 

I tried with this code

Month -1 = calculate(sum(Sales [UNITS]),filter(Sales,dateadd(Sales[Date],-1,MONTH)))

but it doesn´t work correctly. 

 

I believe it is important to mention that I have a record of sales since january 1st, 2018.

Hope you can help me. 

Thank you. 

4 REPLIES 4
gooranga1 Senior Member
Senior Member

Re: Measure that shows the sum of units sold of month selected -1

You could try

 

Month -1 = Calculate(Sum(Sales[Units]),Parallelperiod(Sales[Date],-1,Month)) 

I think that will only work if there are no gaps in your sales data. It would work better using a date dimension.

abisaileon11 Regular Visitor
Regular Visitor

Re: Measure that shows the sum of units sold of month selected -1

Thanks but it still doesn't work. 

I use 

mes-1= calculate(sum(Sales[Units]),filter(Sales,month(Sales[Date])=month(today())-1),filter(sales,year(Sales[Dates])=year(today())))

But It only works with this month. 

Do you know any other way?

Super User
Super User

Re: Measure that shows the sum of units sold of month selected -1

Hi @abisaileon11

 

It looks like you need to have an ALL(Sales[Date]) in your FILTER( ). Otherwise you still have the limitation of the filter that's acting on [Date] (you say you select a month).

You provide no info on how/where exactly you are using the measure.  What fields are acting on the measure? What tables do you have in your model that are involved? Is there a date table?

That is necessary to come up with an accurate solution.

 

 

 

 

 

abisaileon11 Regular Visitor
Regular Visitor

Re: Measure that shows the sum of units sold of month selected -1

Hi thanks for your answer,

I'll explain...

I need to visualice the sales of previous selected month to compare them with stock of selected month.

Sin título.jpg

'Demanda' is for sales of the previous month and Stock is the total in storage of this month.  I accomplished this window using this measures, 

for sales of previous month 

Month -1 =calculate(sum(Sales[Units]),filter(Sales,month(Units[Dates])=month(today())-1),filter(Sales,year(Sales[Date])=year(today())))

And for stock in current month I used 

CurrenMnth = calculate(sum(Stock2[Units]),filter(Stock2,month(Stock2[Date])=month(today())),filter(Stock2,year(Stock2[Date])=year(today())))

So, this works but it only shows information of current month vs previous month, as you can see in the code. What I need to achieve is after I select a month it shows me the informacion of sales of previous month selected and the stock of the month selected.

 

This is also needed to calculate days of stock, the table at the bottom, that is calculated this way:
Days Of Stock= (Sales of previous month selected/Stock of month selected)*30

 

I have information from january 1st, 2018 to now.

It is important to mention that I have a date table

as.jpg

I hope you can help me.