Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I want to get the last 6 months sales ending on the last day of last month from the current date. Following code is what I use. Is there any better suggestion to improve this?
6M pri sales value =
CALCULATE (
SUM ( FactPrimarySales[NetValue] ),
DATESINPERIOD (
DimDate[Date],
CALCULATE (
LASTDATE ( DimDate[Date] ),
DimDate[MonthNo]
= MONTH ( TODAY () ) - 1,
DimDate[Year] = YEAR ( TODAY () )
),
-6,
MONTH
)
)
Solved! Go to Solution.
@Anonymous , Try a measure like this with help from date table
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))
Hi,
If today is December 25, 2020, then i assume that "last day of last month from the current date" should mean 6 months ended November 30, 2020. If my assumption is correct, then try this measure:
=calculate(sum(Sales[Sales Amount]),datesbetween(calendar[date],EDATE(EOMONTH(today(),-1)+1,-6),EOMONTH(today(),-1)))
Hope this helps.
Hi,
If today is December 25, 2020, then i assume that "last day of last month from the current date" should mean 6 months ended November 30, 2020. If my assumption is correct, then try this measure:
=calculate(sum(Sales[Sales Amount]),datesbetween(calendar[date],EDATE(EOMONTH(today(),-1)+1,-6),EOMONTH(today(),-1)))
Hope this helps.
@Anonymous , Try a measure like this with help from date table
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))
User | Count |
---|---|
119 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
177 | |
84 | |
70 | |
64 | |
54 |