Adding date to my measure

[Jodes]

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).

This calculates absorptions for my entire data set, but I would like to create a new measure to show only for December 31, 2018. (Q4 2018)

Thanks!

[edhans · Accepted solution]

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.

[Jodes]

@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.

[Jodes]

Thanks, appreciate it.