Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Rolling Graph missing a months data

Hi have a rolling 3 month graph and for some reason my June 2020 numbers are missing completely

 

ive rebuilt the graph, ensuring i didnt have any filters on and still having the issues. up until the last week ive hadnt had any issues until now.

 

any thoughts on what i can do to fix it? or what i may be doing wrong?

 

my measure formula is 

Closed Effort (Rolling 3 months) =
CALCULATE (
SUM( 'Work Items - All history by month'[Effort] ),
DATESINPERIOD('Date'[Date],LASTDATE(DATEADD('Date'[Date],0, MONTH)), -3, MONTH)
)

 

 

 

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , the formula seems fine.

    Just try this variation using the date from the table too

    Rolling 3 = CALCULATE(SUM( 'Work Items - All history by month'[Effort] ),DATESINPERIOD('Date'[Date],ENDOFMONTH('Work Items - All history by month'[Date]),-3,MONTH))
or
 
Rolling 3 = CALCULATE(SUM( 'Work Items - All history by month'[Effort] ),DATESINPERIOD('Date'[Date ],MAX('Work Items - All history by month'[Date]),-3,MONTH))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 
      thanks for the help

       

      both of those formula's output with a different issue. the %'s are the same for each month

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , hope month year is coming from date calendar.

        Can you share sample data and sample output in table format?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

      your formula isnt that much different to mine

       

      ill see what i can do to replicate this in a sample document

       

      any particular reason why this formula would work and then all of a sudden a month goes missing? there has been data in June 2020 before as i look at it daily

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , In your formula, instead of last day try Max and check

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Please check if there are whole dates in both fact table and date table.

    also, click on three dots on the right-upper corner of the visual and select "show as date", check if there are any strange in the table.

    other concerns like:

    relationships bewteen tables, any visual level/page level filter, other measures in this visual.

     

    If possible, you could delete any privacy data and show us only a little data which can reproduce your problem.

    So that we can test and figure this for you.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-juanli-msft 

      ive checked every tab and graph and no filters on that exclude that month (June 2020)

      even graphs on same tab show june 2020. 
      every other graph has this month, only when i do a rolling 3 month calculation i have hit this problem

      i havent updated my pbix file nor published an update in a few months, so i am not sure why the error has appeared when previously all months were fine

      ive rebuilt the visual and still same issue. not sure where else to look.

       

      am still working on sample data to provide that replicates this issue