Forum Discussion
Adding date to my measure
Here is my measure: Absorptions Per Month = ROUND(SUM('Data by Bedrooms'[Bedroom Sales])/6,0) Absorptions per month is simply all the sales divided by months for the reporting period (6). ...
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
Community Champion
Community ChampionIt 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.
Thanks, appreciate it.