Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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).
Is there a workaround for showing all months in datesinperiod, even if they don't exist in the data?
Solved! Go to Solution.
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
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.
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.
@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
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |