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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

TOPN dax function breaks the date axis

Hello!
I have an 7 days rolling average of brand awareness shown on the line chart. The formula for 7-days rolling average is this:

 
CALCULATE(DISTINCTCOUNT(survey_answers[identity_id]), DATESINPERIOD(DatesTable[Date], MAX(DatesTable[Date]), -7, DAY))
/ CALCULATE(DISTINCTCOUNT(survey_answers[identity_id]), DATESINPERIOD(DatesTable[Date], MAX(DatesTable[Date]), -7, DAY), ALL(survey_answers[Brands])) )
 
 
As you can see I use the date table with all the days from the beginning of data collection till every new day, as I'm still collecting data and will collect for a long time, I should get data everyday, but still sometimes there are some gaps, when data is not fetching for some days. However, as the formula for rolling average works in the way I get the result even for those days I have no data, it works well, and the chart shows each single date(day) on the x-axis. 
There is an issue, I want to have a slicer where a user can select number of TOP brands (as I have a lot of brands in the chart), formula for TOP based on rolling average:
 
CALCULATE(MeasuresTable[%ROLLING_AVG], TOPN(5, ALLSELECTED(survey_answers[Brands]), CALCULATE(MeasuresTable[%ROLLING_AVG], ALLSELECTED(DatesTable)), DESC), VALUES(survey_answers[Brands]))
 

And when it's applied, it breakes the linechart, and shows the results only for those day, when I had the data, so, when I put just rolling avg in the line chart, it shows all the dates, when TOP, only on those I had the data.

Could you help, guys? I've struggling with this for a long time already, idk even what's wrong, maybe you have any ideas. If you need more details, I could provide more, but not the real data, as it's confidential.

2 REPLIES 2
Anonymous
Not applicable

@Anonymous 
Could you please share the result that your are getting currently with your measures.
and please share your expected result with dummy data. so it would be helpful to understand more.

Anonymous
Not applicable

So, let's take a small period, here is where I choose "All" in my switch slicer, this All is assigned a first formula I shared in the main message. 

Gleb_1-1683981233146.png

As you can see, on the axis it shows all the dates, however, in some days during this period I didn't have data, however, I my formula calculates rolling average taking -7 calendar days, it still have some results for those date where I didn't have data.

When I selecting Top 5(second formula from the main message), it works ok, return only 5 top values based on the rolling avg measure, howerer it doesn't show the results for those days I don't have the data, but it use the same measure, just apply topn function.

Gleb_0-1683981196704.png

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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