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

Showing missing months in chart in combination with datesinperiod

I'm trying to show a graph with trends for a specified period of time, depending on the month chosen. 

 

So for example: July 2020 is chosen. I want to show trend of sales data for last 7 months and 4 months after july 2020.

 

I tried testing this with an example from this website:

 

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe... 

 

Which gives the following DAX formula: 

 

 

Sales (last n months) = CALCULATE (

SUM ( Sales[Sales] ),

DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date]), -N[N Value], MONTH )

)

 

 

This works.. however, in my situation I have missing months in the data. I still want to show these months on the X-axis, so thay I always have 1 year in the graph, also irrespective of other filters (e.g. seller A and B, see example PBIX).

 

For similar example see my adjusted PBIX (based on sqljason url).

 

Link to file

 

Is there a workaround for showing all months in datesinperiod, even if they don't exist in the data?

2 ACCEPTED SOLUTIONS

Hi @Anonymous,

Did these missed records really exist in your table or the table only stored the existing records? If they do not contain in your table, you need to create a calendar table with completed date ranges and use this on axis field of the chart.

Then you can write a measure formula to lookup and summarize correspond records based on the current calendar date. 

Notice: power bi chart visual will auto-hide the records that not have correspond values and not display the category with zero values. Perhaps you can try to return a default value to force display them on the chart.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Unfortunately, none of these suggestions worked. What did solve my problem was to create a separate 'disconnected' date dimension table.

 

See this blog for the solution.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

I am not sure whether it is the best solution but in my case (I had to display only one month while keeping the entire axis) I created another DimDate Table and I used it in my X-axis. 

amitchandak
Super User
Super User

@Anonymous , If you have try measure like

measure =
var _min1 = minx(allselected('Date'), 'Date'[Date])
var _min = date(year(_min1),month(_min1)-7,day(_min1)) // before 7 month
var _max1 = minx(allselected('Date'), 'Date'[Date])
var _max = date(year(_max1),month(_max1)+4,day(_max1)) // after 4 month
return
if(max( 'Date'[Date]) >= _min && max( 'Date'[Date]) <=_max , [Sales (last n months)]+0, blank()) //Current measure

Anonymous
Not applicable

@amitchandak Thank you for the response. Unfortunately this doesn't work, since in the missing months the sales don't occur, so the +0 won't happen.

 

@Anonymous I tried this, but it didn't work as expected. It only shows the available months and the selected months (if missing) as blank. So still missing the other 'empty'/missing months.

Hi @Anonymous,

Did these missed records really exist in your table or the table only stored the existing records? If they do not contain in your table, you need to create a calendar table with completed date ranges and use this on axis field of the chart.

Then you can write a measure formula to lookup and summarize correspond records based on the current calendar date. 

Notice: power bi chart visual will auto-hide the records that not have correspond values and not display the category with zero values. Perhaps you can try to return a default value to force display them on the chart.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Unfortunately, none of these suggestions worked. What did solve my problem was to create a separate 'disconnected' date dimension table.

 

See this blog for the solution.

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.