Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Wresen
Post Patron
Post Patron

Sum total of previous month on total month

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
1 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

View solution in original post

6 REPLIES 6
amitchandak
Super User
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
https://www.youtube.com/watch?v=6LUBbvcxtKA

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)

@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

 

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

@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

Hi @amitchandak 

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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