Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to 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 , 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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |