cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jts_
Frequent Visitor

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 IDVisit Date
A6/1/2022
A6/5/2022
A5/3/2022
B2/1/2022
B4/5/2022
B5/5/2022

 

 

For example, when the date slicer set 6/30/2022 as the end date, I would like to calculate the occurrence of each customer in the time range from 5/1/2022- 6/30/2022. The preferred output will be:
slicer.PNG

 

 

 

Max Date on Slicer6/30/2022
Customer IDVisit Counts
13
21

 

 

Similarly, when the end date on the slicer is changed into 5/31/2022, the time range of calculation will switch to 4/1/2022-5-31/2022. The preferred result now will be:

Max Date on Slicer5/31/2022
Customer IDVisit Counts
12
22

 

 

 

Thank you for your assistance in advance!!

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @jts_ ,

 

Here's my solution.

1.Create a calendar table.

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

vstephenmsft_0-1658129501909.png

 

2.Create a slicer for the max date.

vstephenmsft_1-1658129529034.png

 

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,

vstephenmsft_3-1658129991634.png

When the max date is  5/31/2022,

vstephenmsft_4-1658130041005.png

 

 

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.

 

 

 

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

Hi @jts_ ,

 

Here's my solution.

1.Create a calendar table.

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

vstephenmsft_0-1658129501909.png

 

2.Create a slicer for the max date.

vstephenmsft_1-1658129529034.png

 

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,

vstephenmsft_3-1658129991634.png

When the max date is  5/31/2022,

vstephenmsft_4-1658130041005.png

 

 

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.

 

 

 

Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table and a relationship (Many to One and Single) from the Visit Date column of the Data Table to the Date column of the Calendar Table.  To your slicer visual, drag the Date from the Calendar Table.  To your Table visual, drag the CustomerID.  Write this measure

Measure 1 = calculate(countrows(Data),datesbetween(calendar[date],max(calendar[date])-59,max(calendar[date])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
indkitty
Helper II
Helper II

Hi 

I would create a date table from the visit date. Use the below code create a date table. Build the relationship Date.Date to Visit date in your other table.

 

let
   StartDate = List.Min(Table[Visit Date]),
   EndDate = Date.EndOfMonth(List.Max(Table[Visit Date])),
    Source = List.Dates(StartDate,Duration.Days(EndDate - StartDate),#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Day of Week", "YearMonth", each Number.ToText([Year])&Text.Start([Month Name],3)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"YearMonth", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type2", "YearMonthSort", each Text.Combine({Date.ToText([Date], "yyyy"), Date.ToText([Date], "MM")}), type text),
    #"Inserted Week of Year" = Table.AddColumn(#"Added Custom Column", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Week of Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type)
in
    #"Inserted Quarter"

 

 Then build a measure as below.

 

 

CustVisits = 
var eom = eomonth(Max('Date'[Date]),0)
var eom1 = eoMONTH(eom,-2)+1
VAR VISIT= CALCULATE(COUNTROWS('Table'),
           FILTER ( ('Table'),
        'Table'[Visit Date]>eom-60 && 'Table'[Visit Date]<=EOM))
return VISIT

 

 The date selected is 30/05/2022. i.e may. Table displays only April and May.

indkitty_0-1657927783134.png

Raw data displaying 7 rows (unfiltered)

indkitty_1-1657927907787.png

This works for all months as some months may have 28 days, 30, 31 days.

 

Let me know if this works for you.

 

grantsamborn
Responsive Resident
Responsive Resident

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.)

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!

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.