Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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