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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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)
)

 

Innovation.PNG

 

 

8 REPLIES 8
v-juanli-msft
Community Support
Community Support

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
Not applicable

@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

amitchandak
Super User
Super User

@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
Not applicable

@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

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

Anonymous
Not applicable

@amitchandak 

 

thanks i cant seem to get the formula to work 

Anonymous
Not applicable

@amitchandak 
thanks for the help

 

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

Innovation2.PNG

@Anonymous , hope month year is coming from date calendar.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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