cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## Sum total of previous month on total month

Hi

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
1 ACCEPTED SOLUTION
Super User

@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

6 REPLIES 6
Super User

@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

Post Patron

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)

Super User

@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

default date

Post Patron

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())) )

Super User

@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

Post Patron

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors