Forum Discussion

jts_'s avatar
jts_
Icon for Helper I rankHelper I
4 years ago
Solved

HELP! Calculate occurrence dynamically based on date slicer

Hi,   I am looking for a solution to calculate occurrence dynamically for the past 60 days of the maximum date on the slicer. The sample dataset is as below: Customer ID Visit Date A ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jts_ ,

     

    Here's my solution.

    1.Create a calendar table.

    Calendar = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

     

    2.Create a slicer for the max date.

     

    3.Create another measure for counting the visits.

    Visit Counts =
CALCULATE (
    COUNT ( 'Table'[Customer ID] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Customer ID] = MAX ( 'Table'[Customer ID] )
            && [Visit Date]
                >= SELECTEDVALUE ( 'Calendar'[MaxDate] ) - 60
            && [Visit Date] <= SELECTEDVALUE ( 'Calendar'[MaxDate] )
    )
)

     

    4.Create a table visual.

    When the max date is  6/30/2022,

    When the max date is  5/31/2022,

     

     

    Best Regards,

    Stephen Tao

     

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

     

     

     

grantsamborn's avatar
grantsamborn
Icon for Solution Sage rankSolution Sage
4 years ago

Give this a try.

Visit Count =
CALCULATE(
DISTINCTCOUNT(Visits[Visit Date]),
'Visits'[Visit Date] <= MAX(DateSlicer[Date])
&& 'Visits'[Visit Date] >= MAX(DateSlicer[Date]) - 60
)
('DateSlicer' should be a disconnected table (no relationships) consisting of a list of dates.)
jts_'s avatar
jts_
Icon for Helper I rankHelper I
4 years ago

Hi grantsamborn ,

 

Thank you for your response! 
I follwed along your suggestion from creating another "DateSlicer" table that does not have any relationship with other tables.

However, the DAX retrieve the data as I wanted when I calculate all occurence that are on or before the selected date on filter ('Visits'[Visit Date]<=MAX(DateSlicer[Date]) ).
But then, when adding the last part to specify the 60 days window ( &&'Visits'[Visit Date]>=MAX(DateSlicer[Date]) -60 ).
All the result turns into blank. Can you please advise the possible reason of it? Thank you so much!