Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Cactus26
Helper I
Helper I

Measure to calculate how many times the same item occured in past weeks

Hi,

 

I need to find a way to calculate the measure which will count the number of items which occured more than 2 times in last 2 weeks. We need to check just items from current week. It should be made as measure as the output will depend on filters.

 

WeekItemOccurrence
1abc2
1abc2
1cde4
1cde4
1efg5
1uut2
1ooo2
1efg5
2ghi3
2aaa1
2cde4
2cde4
2efg5
2efg5
2ghi3
2lno2
3uut2
3ooo2
3efg5
3ghi3
3lno2

 

Occurence column show calculations (Countif). We are in week 3 and items which occured more than 2 times are: efg and ghi. So final measure should show 2. As two items from current week occured more than 2 times in past 2 weeks (including current).

 

Thank you!

1 ACCEPTED SOLUTION

Hi Cactus,

 

This code looks fine, somewhat tricky, but it does its job.

 

 

Occ =
VAR CurrentWeek = CALCULATE ( MAX ( Test[Week] ), ALLSELECTED ( Test[Item] ) )
RETURN
    COUNTROWS (
        FILTER (
            CALCULATETABLE ( VALUES ( Test[Item] ), Test[Week] = CurrentWeek ),
            CALCULATE (
                COUNTROWS ( Test ),
                Test[Week] >= CurrentWeek - 2,
                Test[Week] <= CurrentWeek - 1
            )
                >= 2
        )
    )

 

Of course, you might need some minor modification and, most important, understand well how it works, since it hides some complexities in the definitino of CurrentWeek and the determination of the items.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

 

Alberto Ferrari - SQLBI

View solution in original post

15 REPLIES 15

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.