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 "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
 

 

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,

3 Replies

  • 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,