Dax coding which might involve Var

[Anonymous]

Hi, new to Power BI, first time poster. I want to analyse information about some short term accommodation we run. I have various rows for every household in accommodation with a column called "tenancy start date" and a column called "tenancy end date". I have already worked out a formula that tells me how may people are in the accommodation at todays date:

No. of households in TA =

CALCULATE(COUNT(TA[TA Tenancy Start Date]),TA[TA Tenancy Start Date]<= TODAY(), TA[TA Tenancy End Date]>TODAY() || TA[TA Tenancy End Date] = BLANK())

However, I want to be able to say how many people were in accommodation at:

a) the end of each month

b) any given date.

I have reached my coding peak i fear and not sure how to progress this. Any thoughts?

Kind regards and thanks for this excellent resource and the fabulous contributors out there!

Peter

[v-cherch-msft]

Hi @Anonymous 

You may create a dates table.And then create a measure like below.Attached sample file for your reference.

Measure =
CALCULATE (
    COUNT ( TA[tenancy start date] ),
    FILTER (
        GENERATE ( TA, Dates ),
        TA[tenancy start date] <= Dates[Date]
            && TA[tenancy end date] >= Dates[Date]
    )
)

Regards,

[Anonymous]

Wow, @v-cherch-msft - you is totally awesome.

However, I forgot to mention. If the household are currently in the accommodation, the column Tenancy end date will be blank. I have tried myself to amend your formula but to no avail. 

Thank you 

Peter

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may add an Update_end date column then modify the measure to get it.

Update_end date = IF(TA[tenancy end date]=BLANK(),TODAY(),TA[tenancy end date])

Measure = 
CALCULATE (
    COUNT ( TA[tenancy start date] ),
    FILTER (
        GENERATE ( TA, Dates ),
        TA[tenancy start date] <= Dates[Date]
            && TA[Update_end date] >= Dates[Date]
    )
)

Regards,