report set up for dates help (dynamically find days since separation/lookup hours based on slicer)?

[alya1]

Hi all this might be a complicated question, apologies!

I'm trying to use multiple source tables to create a report that shows customer points earned and used that DYNAMICALLY filters based on a slicer for each calendar/fiscal year. 
There are 3 main tables (examples below but with many more ID and dates):
customer historical that lists the customer active/their hours on app each day

customer id	historical date	hours
111	1/1/2020	1
222	1/1/2020	1
111	1/2/2020	2

customer summary that lists general information of all customers we have/had

customer ID	status	start date	end date	hours	days since separation (calculated col)	pts earned (calculated col)
111	active	1/1/2020		10	end date - TODAY()	based on hours
222	inactive	1/1/2020	10/10/2020	5
333	active	5/5/2020		15
444	active	5/5/2020		20

customer points history that lists pts used 

customer ID	points date	points	action
111	1/5/2020	5	used
222	1/5/2020	10	used
333	5/5/2020	1	used
444	5/6/2020	2	used

And an autocalendar table that connects to the historical date, summary end date (inactive due to ambiguity), and points date.

I was only able to make the report show 1 year time span based on todays date by mainly using customer summary table and by using page filters for points date (relative date in the last 1 year) and days since separation (0-365 days).

How can I make it so that a single dates slicer using the autocalendar column can show measures for only active customers in those date ranges? Perhaps if there's a way to dynamically find days of separation / look up hours in customer historical table based on last date in the slicer?
I only need calendar and fiscal year ranges if that makes it easier!
Thank you!

[Anonymous · Accepted solution]

Hi @alya1 

Based on your description, it seems that you are trying to calculate the point earned and used results for active users, whereas active accounts are dynamically determined based on the slicer. Please correct me if I'm misunderstanding.

Create the following measures:

IsActive = 
CALCULATE(
    IF(
        AND(
            MIN('customer summary'[start date]) <= MAX('autocalendar'[Date]),
            OR(
                ISBLANK(MIN('customer summary'[end date])),
                MIN('customer summary'[end date]) >= MIN('autocalendar'[Date])
            )
        ),
        1,
        0
    ),
    FILTER(
        'customer summary',
        'customer summary'[customer ID] = MAX('customer summary'[customer ID])
    )
)

PointsEarned = 
CALCULATE(
    SUM('customer summary'[pts earned]),
    'customer summary'[start date] <= MAX('autocalendar'[Date]),
    OR(
        ISBLANK('customer summary'[end date]),
        'customer summary'[end date] >= MIN('autocalendar'[Date])
    )
)

PointsUsed = 
CALCULATE(
    SUM('customer points history'[points]),
    'customer points history'[points date] <= MAX('autocalendar'[Date])
)

ActiveCustomers = 
CALCULATE(
    COUNTROWS('customer summary'),
    'customer summary'[start date] <= MAX('autocalendar'[Date]),
    OR(
        ISBLANK('customer summary'[end date]),
        'customer summary'[end date] >= MIN('autocalendar'[Date])
    )
)

Create a table visual and apply the filter: "IsActive is 1" in the Visual Filters panel.

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

[alya1]

Thank you so much Jarvis! This is getting super close to what I need!!
One thing is that the Points Earned column in a calculated column based on Hours which, similar to active accounts, dynamically changes based on the slicer. Do you have any insight for this part as well please? 
This part is probably harder because the dynamic hours will be used to calculate the points earned column (this calculated col has super long dax) 

[Anonymous]

Hi @alya1 

I think I understand what you mean, please try to create the following measure:

PointsEarned = 
SUMX(
    VALUES('customer summary'[customer ID]),
    CALCULATE(
        SUM('customer historical'[hours]),
        FILTER(
            ALL('customer historical'),
            'customer historical'[customer id] = MAX('customer summary'[customer ID]) &&
            'customer historical'[historical date] <= MAX('AutoCalendar'[Date])
        )
    )
)

In my test, I added some data to the sample data table customer historical:

(Ensure that each customer's total hours is equal to the data in the customer summary.)

Here is my final result:

Similarly, [PointsUsed] can be used in the same way if desired.

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

[alya1]

Thank you so much Jarvis for all your help! I think I can do it now 🙂 
I edited your dax a bit to get the Hours specific to that day instead of sum as below: 

HoursAtMaxDate = 
SUMX(
    VALUES('customer summary'[customer ID]),
    CALCULATE(
        SUM('customer historical'[hours]),
        FILTER(
            'customer historical',
            'customer historical'[customer id] = EARLIER('customer summary'[customer ID]) &&
            'customer historical'[historical date] =  MAX('autocalendar'[Date])
        )
    )
)

[darkniqht]

Ensure your Autocalendar table is properly related to your other tables—Customer Historical, Customer Summary, and Customer Points History. This will help filter data correctly.

You can create a measure to filter out inactive customers based on the slicer:

ActiveCustomers =
CALCULATE(
    COUNTROWS('Customer Summary'),
    'Customer Summary'[status] = "active"
)

Create measures for points earned and points used that only consider active customers within the selected date range:

PointsEarned =
CALCULATE(
    SUM('Customer Summary'[pts earned]),
    'Customer Summary'[status] = "active",
    'Autocalendar'[Date] >= MIN('Autocalendar'[Date]) &&
    'Autocalendar'[Date] <= MAX('Autocalendar'[Date])
)

PointsUsed =
CALCULATE(
    SUM('Customer Points History'[points]),
    'Customer Points History'[action] = "used",
    'Customer Summary'[status] = "active",
    'Autocalendar'[Date] >= MIN('Autocalendar'[Date]) &&
    'Autocalendar'[Date] <= MAX('Autocalendar'[Date])
)

Add a slicer for the Autocalendar date. The measures you created will automatically adjust based on the selected date range.

Create visuals using the measures to display points earned and used. They should now reflect only for active customers within the selected date range.

[alya1]

Hi Darknight, thank you for your response! I thought about it and it's missing a piece of incorporating the Customer Historical table. Because the customer overview data is always the most current but if we are filtering say last year, then some of the inactive customers will be active. So I'm trying to see if it's possible to look up hours in customer historical table based on last date in the slicer and if there are no hours then return blank since it means they were not active that day + dynamically find days of separation since I am still thinking in the 0-365 seperation days route.