active users over a period

[Anonymous]

Hi,

I have a requirement where i need to find the count of active user per month/Qtr/Year based on slicer selection from the sample data attached here.The active or inactive status is described by Flag column.

If we have a slicer from date dimension,it should check if user is belonging in that range between start and end date.If the user belongs to the window and flag=true ,then the user is to be counted in the headcount.

Pls guide as i am not sure how to do this without a relationship between fact and date tables.

Note: Data in the date table contains all dates from 1/1/2020 to 12/31/2023

[lbendlin]

You are wasting space by presenting a calendar/dates table that goes far beyond the current date.  No user can have been active in the future (unless you know special tricks?) so the first thing you want to do is to cut the dates table down to cover just the actual data range.

Next you can create relationships between the calendar table and the Start and End dates in your fact table. Both relationships can be inactive.

And finally you can use CROSSFILTER(,,none) to create a cartesian product between your fact table Login ID and/or Application which then allows you to determine if a particular user was using a particular app on a particular day.

Lastly you can use the Active flag as a filter for the summary count.

[Anonymous]

Hi,

Thanks for looking into this.To answer your question in comment:

• "You are wasting space by presenting a calendar/dates table that goes far beyond the current date.  No user can have been active in the future"

I would like to clarify here that the requirement i received is such where they are doing a long term forecast and that is the nature of the data.It is assumed that they are going to be active and further there is a requirement of allocating budget for each of application into this model.I am also attaching that budget fact table in the model which will clarify the end goal.End goal is to find out cost per application per month per loginID.Then roll up to different levels.

Model https://drive.google.com/file/d/16rCk0jSJwZ25fDCZbsVzp0yYxk0kU4LZ/view?usp=sharing 

• Can you please show and tell on the model i am attaching here.

[lbendlin]

Your yearly budget amount per app doesn't vibe with the data model, so the budget table will have to stay disconnected, and you will have to use LOOKUPVALUE for any annual measures.

"End goal is to find out cost per application per month per loginID"

Which visual are you planning to use fo that, and when does the budget come into play?

Your sample data is inconsistent, the budget mentions PowerBI (sic) and the usage mentions PowerApp (sic). I took the liberty to change both to Power BI.

[Anonymous]

Sorry for the confusion.I fixed that application name inconsistency.Here is where i am currently in the model with just headcount calculated but i dont know how to calculate the costs per application.I have referred to plenty of resources earlier for a month but no luck in designing this with the budget fact as the granularity is different.I have been unsuccessful in building correct relationships betwen tables to get this working.

To answer your Q:

Which visual are you planning to use fo that, and when does the budget come into play?

There is an amount budgeted for each application forecast upto 2023.Requirement is to find how much is per user cost per application on a given month at lowest level,then roll up to qtr,year.The per user cost is budget amount /headcount

I also felt since there are 2 facts- usage and budget,i am not sure how to get the costs.I couldnt do facts consolidation as well.

I will be really grateful to you and happy if you can help me with the cost measure.

Attaching the model with the fixes.I just created a calculated table Budget_F with monthly granularity to try something,so pls ignore if it is not required.

https://drive.google.com/file/d/16rCk0jSJwZ25fDCZbsVzp0yYxk0kU4LZ/view?usp=sharing 

[lbendlin]

Having a monthly budget number will help if you can also to have month based user license terms. 

How likely is it for users to have start or end dates within a month, and can that be ignored? Say the factual start date is September 20 but for the report it would be treated as September 1?

[Anonymous]

The factual start date will always be month begining and the end date will never be within the month.

Example of use case in the dataset-

User John started on Jan 2020,and quits by Jul 2020.

The he is rehired in Oct 2020 and stays until Dec 2023.

Thanks a lot for looking into it.Please help with this request.

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

If not , what is the expected output of the matrix when the slicer selects ‘2020-05’?

Best regards,
Lionel Chen

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

[lbendlin]

Attached is the modified pbix.  I modified the data model and prepared the budget measure but will leave it as an exercise for you to decide what to do with the totals and how to handle periods other than months.  Calculating total active users across applications doesn't make much sense to me.

Please also note the changes that I made to simplify some of your calculated columns.

[Anonymous]

I looked into the model and see that the total active users measure does not show the count whereas the cost shows up fine.Can you please help.

[v-lionel-msft]

Hi @Anonymous ,

Please show the formula of the measure and related screenshots.

Best regards,
Lionel Chen

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

[Anonymous]

Thanks a lot for your help.Really appreciate it.I will look your solution and get back for any Qs.