Forum Discussion

bcarroll's avatar
bcarroll
Frequent Visitor
3 years ago
Solved

Date bin intervals cutting off last bin

 

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.

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      bcarroll
      Frequent Visitor
      3 years ago

      The first measure led to the desired behavior.  Thanks!