cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aklaur Frequent Visitor
Frequent Visitor

Strange Spike in Line Chart when using Rolling Year Formula.

 there is a strange Spike at the End of a Line Chart when using a Rolling Year Formula and going Down to any Level on the Chart below Years. I've tried

1 Adjusting the X Axis to filter days Before Current Date.

2) Using a date Filter  for the Visual based on final date in Data Set. Ive Never had a problem with this in Excel (i.e  Rolling- year formula, at a MM_YYYY level this is the Rolling year Formula I am using:

MIC_RY1_all = CALCULATE(Sum(FACT_MIC[Units]),DATESBETWEEN(DATE_DIM[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(DATE_DIM[Date]))),LASTDATE(DATE_DIM[DATE])))

 Rolling Year at a QQYY level:

Capture1.PNG

At a Year level , no spike:

Capture2.PNG

 

 

Rolling year formula at a sub-year level in Excel ( No issue )

Capture3.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-lid-msft Super Contributor
Super Contributor

Re: Strange Spike in Line Chart when using Rolling Year Formula.

Hi @aklaur ,

 

To use Time intelligence function, Continuous time is necessary for most time. you can try to generate a calendar table and using in your DAX.

 


If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Best regards,

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Strange Spike in Line Chart when using Rolling Year Formula.

Hi @aklaur 

Can you share the pbix, or a simplified version that reproduces the issue?

v-lid-msft Super Contributor
Super Contributor

Re: Strange Spike in Line Chart when using Rolling Year Formula.

Hi @aklaur ,

 

To use Time intelligence function, Continuous time is necessary for most time. you can try to generate a calendar table and using in your DAX.

 


If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Best regards,

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

View solution in original post

aklaur Frequent Visitor
Frequent Visitor

Re: Strange Spike in Line Chart when using Rolling Year Formula.

  Yes thank you for the Tip, which seems obvious to me now. I have a Calendar table called DATE_DIM but I didn't specfically designate it as a Calendar Table. This solved the Spike problem. thank you!

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 374 members 3,454 guests
Please welcome our newest community members: