Forum Discussion
Adding date to my measure
Here is my measure:
Absorptions Per Month = ROUND(SUM('Data by Bedrooms'[Bedroom Sales])/6,0)
If you have a Dates table, it would be something along the lines of:
Absorptions Per Month = CALCULATE ( ROUND ( SUM ( 'Data by Bedrooms'[Bedroom Sales] ) / 6, 0 ), FILTER ( Dates, Dates[Month] = 12 ), FILTER ( Dates, Dates[Year] = 2018 ) )Assuming your Dates table is Dates, and you have a Month and Year column in it.
4 Replies
- edhans
Community Champion
If you have a Dates table, it would be something along the lines of:
Absorptions Per Month = CALCULATE ( ROUND ( SUM ( 'Data by Bedrooms'[Bedroom Sales] ) / 6, 0 ), FILTER ( Dates, Dates[Month] = 12 ), FILTER ( Dates, Dates[Year] = 2018 ) )Assuming your Dates table is Dates, and you have a Month and Year column in it.
edhans, I don't have a date table. Is there another way to do this? Or is creating a dates table the best option?
- edhans
It is the best option.
You can try to do it using the built in dates table when you don't create one, but the syntax gets tedious. It would begin to look something like this:
Test = CALCULATE( SUM(Sales[Sales]), FILTER(Sales,[Date].[MonthNo]=1) )But if your Power BI model has dates, create a date table. Just make it a habit. You'll never build a model with a date table and wish you hadn't. You'll often build a model without a date table and wish you had done so from the start.
This article will help you quickly build a dynamic Date table for your model.