Forum Discussion

nhutruong's avatar
nhutruong
Frequent Visitor
3 years ago
Solved

DAX command for count records within a time range

Hi everyone,

 

I want to find a dax command to return the result which looks something like the table below (Table 1). I have another master table where each items appear multiple time with a specific time stamp, i want a dax formula for "Appearance in past 6 months" where it would count how many time items appear from (6-Jul-22 to 2-Jan-23)

 

ItemsWeekWeek Start DateStart Count Date DateAppearance in Past 6 month
AW12-Jan-236-Jul-22 
BW29-Jan-2313-Jul-22 
CW316-Jan-2320-Jul-22 
DW423-Jan-2327-Jul-22 

 

Thank you so much in advance.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi nhutruong ,

     

    I suggest you to try this code to create a measure.

    Appearance in Past 6 month =
CALCULATE (
    COUNT ( 'MasterTable'[Item] ),
    FILTER (
        'MasterTable',
        'MasterTable'[Item] = MAX ( 'Table1'[Item] )
            && 'MasterTable'[Timestamp] >= MAX ( 'Table1'[Start Count Date] )
            && 'MasterTable'[Timestamp] <= MAX ( 'Table1'[Week Start Date] )
    )
)

    Or calculated column:

    Appearance in Past 6 month =
CALCULATE (
    COUNT ( 'MasterTable'[Item] ),
    FILTER (
        'MasterTable',
        'MasterTable'[Item] = EARLIER ( 'Table1'[Item] )
            && 'MasterTable'[Timestamp] >= EARLIER ( 'Table1'[Start Count Date] )
            && 'MasterTable'[Timestamp] <= EARLIER ( 'Table1'[Week Start Date] )
    )
)

     

    Best Regards,
    Rico Zhou

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi nhutruong ,

     

    I suggest you to try this code to create a measure.

    Appearance in Past 6 month =
CALCULATE (
    COUNT ( 'MasterTable'[Item] ),
    FILTER (
        'MasterTable',
        'MasterTable'[Item] = MAX ( 'Table1'[Item] )
            && 'MasterTable'[Timestamp] >= MAX ( 'Table1'[Start Count Date] )
            && 'MasterTable'[Timestamp] <= MAX ( 'Table1'[Week Start Date] )
    )
)

    Or calculated column:

    Appearance in Past 6 month =
CALCULATE (
    COUNT ( 'MasterTable'[Item] ),
    FILTER (
        'MasterTable',
        'MasterTable'[Item] = EARLIER ( 'Table1'[Item] )
            && 'MasterTable'[Timestamp] >= EARLIER ( 'Table1'[Start Count Date] )
            && 'MasterTable'[Timestamp] <= EARLIER ( 'Table1'[Week Start Date] )
    )
)

     

    Best Regards,
    Rico Zhou

     

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