Counting Items in Dimension Table based on Date

[JP8991]

Hello,

Probably a really simple one but my brain is a little fried today.

I am trying to count the number centres in my dimension table however want to account for closures (Divestment Date) so for example a centre closes in 2020 so it would be present in 2019 and 2020 but not present in 2021 and 2022.

Trying to get the below table to reflect the closed centres, calendar year is taken from the Calendar table above.

[danextian]

Hi @JP8991 ,

Your screenshot doesn't show the relationship between Centres and Calendar but if you're getting the same figure for all calendar years then mostly  likely that there isn't. You can try this as a measure but still won't show the correct number if you use calendar years and there isn't a relationship between the two tables

=
CALCULATE (
    COUNTROWS ( Centres ),
    FILTER (
        ALL ( Centers[Divestment Date] ),
        Centers[Divestment Date] <= MAX ( Centers[Divestment Date] )
    )
)

[JP8991]

Thanks, really appreciate it, however it isn't quite working.

Centres = 
CALCULATE (
    COUNTROWS ( Centres ),
    FILTER (
        ALL ( Centres[Divestment Date] ),
        Centres[Divestment Date] <= MAX ( Centres[Divestment Date] )
    ),
    USERELATIONSHIP ( 'Calendar'[Date], Centres[Divestment Date] )
)

Using the above code I am getting the table below which is counting the number of closed Centres, what I am after is the number opposite to that, I am after the amount that were open.

So in 2019 it would be 45 minus total, in 2020 it would be 45 + 7 minus total etc etc.

[v-tangjie-msft · Accepted solution]

Hi @JP8991 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a measure. 

Centres = 
var _a=
CALCULATE (
    COUNTROWS ( Centres ),
    FILTER (
        ALL ( Centres[Divestment Date] ),
        Centres[Divestment Date] <= MAX ( Centres[Divestment Date] )
    )
    
)
var _b=COUNTROWS(FILTER(ALL('Centres'),'Centres'[Divestment Date]))
return 
_a-_b

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@JP8991 , Seem like join is missing between Divestment Date, or create an inactive join and userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...