I'm am trying set up a calculation that will allow me to display the number of new items per week, for every week of the year as it goes on. I get a nightly report containing a list of current item numbers and the date of the report. As items are processed they are removed from the list, some items can be on the daily list multiple days and weeks and some might be on the list for only a day. We store the data in a table:

I'm am trying set up a calculation that will allow me to display the number of new items per week, for every week of the year as it goes on. I get a nightly report containing a list of current item numbers and the date of the report. As items are processed they are removed from the list, some items can be on the daily list multiple days and weeks and some might be on the list for only a day. We store the data in a table:

Explanation: The first week of January (01/03/2025) we had 3 items and the 2nd week (01/06-01/07) we had 5 items and 3 new items. So for week 2 of my bar chart I would have a count of 3. I also have a date table with year, weekofyear, month, and day values. I don't know how to calculate that for a week after week calculation of new items. I have looked online but I keep getting calculations for Week over Week - which causes the values to go up weekly. I think I have to use a combination of measures and a table but not sure. I have set up a "this weeks items" in a table and "last weeks items" in a table and the used the EXCEPT function to get the items that are in one table but not the other.

Need more help though. Also, I need to account for week 52 of the last year and week 1 of the current year. And as each week goes by, want the bar chart to reflect the new items for that week.

Example of data list

• powerbi
• dax

1 Answer 1

You'll need to get the previous week's Items and filter on the current week to not include those.

You can try a measure similar to:

Weekly new items = 
  // get current week and year in visual context
  var thisYear = MAX(DateTable[year])
  var thisWeek = MAX(DateTable[weekofyear])

  // determine previous week
  var prevWeek = IF(thisWeek = 1, 52, thisWeek - 1)
  var prevWeekYear = IF(thisWeek = 1, thisYear - 1, thisYear)

  // get prevWeek items
  var prevWeekItems = 
    CALCULATETABLE(
      DISTINCT( YourTable[item number] ),
      REMOVEFILTERS(DateTable),
      DateTable[year] = prevWeekYear  && 
      DateTable[weekofyear] = prevWeek 
    )

  // current week's items
  var thisWeekItems = DISTINCT( YourTable[item number] )

  var result = COUNTROWS( EXCEPT( thisWeekItems, prevWeekItems ) )

  return result