Forum Discussion

JP8991's avatar
JP8991
Icon for Kudo Commander rankKudo Commander
3 years ago
Solved

Counting Items in Dimension Table based on Date

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.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      JP8991
      Icon for Kudo Commander rankKudo Commander
      3 years ago

      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.




      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.