Date bin intervals cutting off last bin

[bcarroll]

My objective is to build a time series chart that shows the number of errors by week.  I have a data set that includes a "Date Detected" field that has been identified as date field and has the typical year/month/day hierarchy developed within PowerBI Desktop.  To build the chart, I grouped based on the date detected.  I used a bin type of "size of bins" and set the bin size to 7 days.  Below is a screenshot of what I did....

And the results look like this.....

The problem is this.....   Whenever I refresh the data, it short changes the final bin which creates the impression that the defect volume just decreased significantly.  All of the bins but the last bin are 7 days.  The last bin is the remainder, so sometimes it is 1 day, sometimes 2 days, etc.  As an experiment, I put a time slicer on this... and it almost always short changes the last bin no matter what I set up as the start and end date.

[v-yalanwu-msft · Accepted solution]

Hi, @bcarroll ;

So All you don't want to show the last date group that doesn't meet 7 days, right? If so, you can create such a measure.

Flag= 
 VAR _NUM=WEEKNUM(MAX( [Date])- WEEKDAY(CALCULATE(MIN([Date]),ALLSELECTED('Table')),2),1)
 VAR _MAX=WEEKNUM(CALCULATE(MAX( [Date]),ALLSELECTED('Table'))- WEEKDAY(CALCULATE(MIN([Date]),ALLSELECTED('Table')),2),1)
 RETURN IF(CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALLSELECTED('Table'),WEEKNUM([Date]-WEEKDAY(MIN('Table'[Date]),2),1)=_MAX))=7,1,IF(_MAX<>_NUM,1))

Then apply it into filter.

Or

flag2 = 
var _max=WEEKNUM(CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table')),1)
return 
IF(
CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALLSELECTED('Table'),WEEKNUM([Date],1)=_max))<7,1,
IF(WEEKNUM(MAX('Table'[Date]),1)<>_max,1))

If not right, can you share what you want to output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bcarroll]

The first measure led to the desired behavior.  Thanks!