Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dax coding which might involve Var

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 "tenanc...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,