Sum total of previous month on total month

[Wresen]

Hi

I have search around and have not found anything that works.

I have a calander table that has a relationship to my sales table.

Sales table has Salesdate ,Salesmonth (YYYYMM) and Sales (numbers)

I get this measure to work. (it gives me , 2021-01-05 00:00:00)

Pmonth = DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) 

Is it possible to get the total sum for month 202101 , all sales that were in 202101 (not only from 2021-01-05 ->)

It needs to be in a Mesure and i want the Total sum value to be a "locked" value (it should show the vaule for 202101 even if i add the measure in a table with YYYYMM)

Thanks so much

[amitchandak]

@Wresen , if you have date table you can get last month like given examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Wresen]

Hi @amitchandak  and thanks so much

I might have been edit my question while you were writing an answer.

I do get your measure to work but i need to have the value locked.

What i mean by that is that i want the total sum for sale amount for 202101 (prevous month) but this value should not be linked to date or a month.

I would like to be able to just drag the meaure into a blank table and the total sum value for 202101 is there

(now i need to add a date/month column)

[amitchandak]

@Wresen , You can get working without date slicer. If you stop your calendar on the current month. Otherwise, you need to have a slicer to filter the current of the past month

Time intelligence

https://www.youtube.com/watch?v=OBf0rjpp5Hw

default date

https://www.youtube.com/watch?v=hfn05preQYA

[Wresen]

Hi and thanks so much @amitchandak 

So it is not possbile to do someting like : 

(but for the full month -1)

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]), Filter( DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) )

[amitchandak · Accepted solution]

@Wresen , You can have, Example

measure =
var _max = eomonth(today(),-1)
return
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]), Filter( all('DATE'), eomonth('DATE'[Date],0) =_max ))

measure =
var _max = eomonth(today(),-1)
var _min = eomonth(today(),-2)+1
return
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]), Filter( all('DATE'), 'DATE'[Date] <=_max && 'DATE'[Date] >=_min ))

or filter on date of your table, in place of date table date

[Wresen]

Hi @amitchandak 

Thanks so much , that worked like i charm , that solved all my problems.