Data for only active user in specific range of dates

Power BI
1

HI ,

I need help how to solve this in best practice .

I have Date dim , Users dim and Sales fact. I need to summarize sales by users and show only
active users . I got slicer by dates
But when I filter only active users that change’s also data from previous month ,so I need
show data for user that been active in slicer selected dates .
In user dim I have start day and end day ( active user have today date ; inactive the date of modification)

2

Hi,

It would be grt help if you share a demo pbix file .So i can play with the data and try to solve your problem.

Thanks,
Anurag

3

temp.pbix (583.3 KB)

4

@levitandaniel1977

First I created the Unique Agent ID by Appending the Agent ID’s both from Fact Table Dim Users Table and then created the relationship:

2
21920×1080 132 KB
1
11920×1080 129 KB

Then created the two simple measures one for Sales and one for Sales only for Active Users

Sales =
SUM(Transactions[Deposit_Amount])

Sales for Active Users =
CALCULATE(
[Sales],
FILTER(Users,
Users[status] = “Active”))

Attaching the PBIX file and source files for your review. I extracted the source files from your model as I had to transform it in Power Query.
temp.pbix (589.0 KB) DW Fact_Transactions.csv (1.7 MB) DW v2_dim_users.csv (41.5 KB)

Let me know if this is what you are looking for.

Thanks.

5

Hi @levitandaniel1977, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

6

HI ,
Thank you for answer but this don’t help me ,
The problem is then I filters active it’s filters history also. I will give example .
Annotation 2020-11-04 101705

7

@levitandaniel1977
It seems to be working fine on my end. When I filter active, the result is all active in the Table,

1
11920×1080 173 KB

Or you can elaborate more by providing the pictures of results inside the PBI desktop.

Thanks.

8

temp.pptx (39.2 KB)

9

@levitandaniel1977
I changed the measure to dynamically show the sales only when the Agent was active:

Sales for Active Users Only =
VAR InactiveDate =
MAX( Users[end_date] )
Return
CALCULATE(
[Sales],
FILTER(
‘Dim Dates’,
‘Dim Dates’[Date] < InactiveDate
)
)

Now if we look at Agent 836, he started on 8/16/2020 but his first sale was on 9/1/2020. When you filter by Date, it will start showing the Agent Sales from the date when he first made his sale. The sale is shown for 836 when the date Range is selected from 9/1/2020.

1
11920×1080 173 KB

But if we select the Range before his first sale date, the Table wont show the Agent 836 Sales

2
21920×1080 173 KB

Thanks.