cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
win_toeknee Member
Member

Continuous Count

Target Results: 

Is to have a count of memberships which will give me a total number within a selected period of time. The membership terms are for 1 year.

- The goal is to be able to drilldown from Year/Quarter/Month/Week level.

- Ex: If I look up on January 1, there is 1 membership because Member A started their membership on Jan 1. If I look up on February 1, there are 3 memberships because Member A still has a membership, Member C started their membership on Jan 3, and Member D started their membership on Feb 1.

- Please see below for reference.

- The ultimate goal is to be able to plot this on a time based bar chart which will drill down to Year/Quarter/Month/Week. If I click on any selection (ex; random week), it will tell me the number of active memberships for that specific week. 

 

(Daily Output)

Membership

Jan/01

Jan/02

Jan/03

Jan/04

Feb/01

A

1

1

1

1

1

C

0

0

1

1

1

D

0

0

0

0

1

Total

1

1

2

2

3

(Monthly Output)

Membership

Jan

Feb

A

1

1

C

1

1

D

0

1

Total

2

3

 

 

Current Results/Problem To Solve: 

I am unable to figure out how to get this to work correctly. 

(Incorrect Daily Output)

Membership

Jan/01

Jan/02

Jan/03

Jan/04

Feb/01

A

1

0

0

0

0

C

0

0

1

0

0

D

0

0

0

0

1

Total

1

0

1

0

1

 

 

 

 

 

 

(Incorrect Monthly Output)

Membership

Jan

Feb

A

1

0

C

1

0

D

0

1

Total

2

1

 

Current Incorrect DAX = 

ActiveMembershipCount =
CALCULATE( COUNT( Contracts[members] ), FILTER( Contracts, Contracts[fr_datetime] <= LASTDATE( 'Calendar'[Date] )
|| Contracts[to_datetime] >= FIRSTDATE( 'Calendar'[Date] ) )

 

Any support you are able to provide will be greatly appreciated!

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Continuous Count

HI @win_toeknee ,


I'm not so sure for your data structure, please share a pbix file with some sample data so that we can test on it.

 

In addition, you can also try to use following measure if it works:

ActiveMembershipCount =
VAR _date =
    ALLSELECTED ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNT ( Contracts[members] ),
        FILTER (
            ALLSELECTED ( Contracts ),
            Contracts[fr_datetime] <= MAXX ( _date, [Date] )
                && Contracts[to_datetime] >= MINX ( _date, [Date] )
        ),
        VALUES ( Contracts[members] )
    )

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |