Storing Measure output per date

[patkang]

Dear community, I mave 3 Measure outputs and one Sum from a data table that I would like to store in a separate table: 

The data from my measures are based on a Distinctcount function of userID and is regarding the engagement of a learning platform. 

This value will change everytime i refresh the data source, is it possible to store it in a table so it will look like this?

Date	Active users	Registered Users	Inactive Users	Total Sessions
15.12.2020	47	84	37	128

The Measures are super basic 

Active Users =

SUMX(

VALUES(LearnerActivity[USER ID]),                 -- This contains the table of each login from users,

CALCULATE(

DISTINCTCOUNT(LearnerActivity[USER ID])  -- Each user is to be counted only once

))

Registered Users =

DISTINCTCOUNT(

UserSummary[USER ID]  -- These are the summary all users

)

Inactive Users =

Registered Users - Active Users

Do I need to modify the existing measures to receive a running total for each?

thank you very much for your support in advance, a privilege to be part of this amazing community!

[v-zhenbw-msft · Accepted solution]

Hi @patkang ,

We can delete the relationship between calendar and active user, then we can create a measure to meet your requirement.

1. Delete the relationship.

2. Change the Active user measure like below. Then we can create a new measure to get the running total.

Active Users = 
CALCULATE(DISTINCTCOUNT('Active Users'[USER ID]),FILTER('Active Users','Active Users'[New ACCESS]=MAX(CalendarLookup[Date])))

Running Total Active User = 
CALCULATE(DISTINCTCOUNT('Active Users'[USER ID]),FILTER(ALLSELECTED('Active Users'),'Active Users'[New ACCESS]<=MAX(CalendarLookup[Date])))

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[patkang]

@v-zhenbw-msft Thank you very much indeed. That solves my problem and will help me do it in other cases as well! 

With best regards,

Patrick

[v-zhenbw-msft]

Hi @patkang ,

Do you want a Multi-row-card like this?

If yes, you need to create a measure to get the max date.

We found the blank when we create a relationship between First Access and Calendar date.

So we create a new Access column and rebuilt the relationship based on it.

New ACCESS = DATE(YEAR('Active Users'[FIRST ACCESS]),MONTH('Active Users'[FIRST ACCESS]),DAY('Active Users'[FIRST ACCESS]))

Then we can create a max date measure to get the result.

New Date = 
MAX('Active Users'[New ACCESS])

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[patkang]

@v-zhenbw-msft Thanks a lot for your support. Your answer was very helpful.

What I would need in addition:

Is there a way to capture the running total of active users in a separate measure?

For example if we have 10 new users for 3 days, the running total of active users would be 30.

Appreciate your help a lot!

[patkang]

@MFelix Thank you for your answer!

Unfortunately, only the "Active Users" have a date of the login date, the "registered users" are based on a report that depends on the day that I download it from. The users will appear on the list once they logged in for the first time. 

I would like to share some sample data with you but as I never done it, how can i best share it?

Thank you for your help!

[MFelix]

Hi @patkang 

You can share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

Also please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

[patkang]

@MFelix

https://drive.google.com/drive/folders/1iQ1CiOaQLCTPUzHnc2TpghyUMwi1P8ju?usp=sharing

i've uploaded a copy of the pbix file to google drive. I simplified it so only the data in question is in te model. As you can see the data already changed after my ost due to the updates of the source files. I would like to store these data as per day so i can analyze the growth or declining numbers per date.

Thank you!

[MFelix]

Hi @patkang ,

Measures are calculated at the moment that you run them so they are not stored within the data source and you cannot save it to your model.

Looking at the information you are providing does this table have all the logins for each date?

If this is true then you can create a cumulative measure based on the login date an no need to save it on a table.

If this is not possible them believe that you need to create a new table with the current date and then use the incremental refresh to get the information has you need.

Can you share a sample data?