Forum Discussion

alya1's avatar
alya1
Icon for Helper V rankHelper V
1 year ago
Solved

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

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 sli...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

darkniqht's avatar
darkniqht
Icon for Advocate I rankAdvocate I
1 year ago

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's avatar
    alya1
    Icon for Helper V rankHelper V
    1 year ago

    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.