Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Community
Very good night
I would like to ask for your cooperation to get a measure.
I need to get the sale of the last 3 months excluding the current one.
I've searched for a lot of information but I can't figure out where I can fail to create such a measure.
Here's the syntax
Solved! Go to Solution.
Hi @Hvaldes ,
You may create a calendar table firstly, then create relationship between the fact data table and the calendar table on date field.
Calendar= CALENDARAUTO()
Then you may create measure like DAX below.
Last_3_Month sales =
VAR _LastDate =
LASTDATE ( 'Billing MRS'[Date] )
RETURN
CALCULATE (
SUM ( 'Billing MRS'[Sales] ),
FILTER (
ALLSELECTED ( 'Billing MRS'),
Calendar[Date] < LastDate_
&& Calendar[Date] >= DATEADD ( _LastDate, -4, MONTH )
)
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hvaldes ,
You may create a calendar table firstly, then create relationship between the fact data table and the calendar table on date field.
Calendar= CALENDARAUTO()
Then you may create measure like DAX below.
Last_3_Month sales =
VAR _LastDate =
LASTDATE ( 'Billing MRS'[Date] )
RETURN
CALCULATE (
SUM ( 'Billing MRS'[Sales] ),
FILTER (
ALLSELECTED ( 'Billing MRS'),
Calendar[Date] < LastDate_
&& Calendar[Date] >= DATEADD ( _LastDate, -4, MONTH )
)
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Hvaldes , Try like this with a date table
Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Hi @Hvaldes ,
You will need a Calendar Table.
Try this measure
Last_3_Months =
CALCULATE (
SUM ( 'Billing MRS'[Sales] ),
DATESBETWEEN (
'Calendar'[Date],
EDATE (
MIN ( 'Calendar'[Date] ),
-3
),
MIN ( 'Calendar'[Date] ) - 1
)
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |