Forum Discussion
display months without no data
Hi,
I follow the following blog http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-power-bi.html in order to display Display Last N Months & Selected Month using Single Date Dimension in Power BI.
I've an issue when trying to display month-year when there is no data in the fact table sales.
I did modification: https://1drv.ms/u/s!Amd7BXzYs7AVg3xJ1MKPYI_PIw3z
How to show to show for example October, november and december 2015 as an example?
Ah, your situation is a bit different because of the way you want to do the x-axis. I got it working as follows:
Sales (last n months) = VAR NumOfMonths = - [N Value] VAR ReferenceDate = MAX ( 'Prev Date'[Date] ) VAR StartDate = EOMONTH ( ReferenceDate, NumOfMonths ) VAR Dates = FILTER ( VALUES ( 'Date' ), 'Date'[Date] <= ReferenceDate && 'Date'[Date] > StartDate ) VAR Result = CALCULATE ( SUM ( Sales[Sales] ), Dates ) RETURN IF ( ISEMPTY ( Dates ), BLANK(), Result + 0 )
5 Replies
This is a bit tricky since you want your chart to show more than what you have selected and your Sales table doesn't have any rows for before 2016.
You should be able to follow this approach with the additional tweak of making NumOfMonths dynamic:
https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/AlexisOlson I tried and it seems that I'm too close. But I'm not able to show month without data; I tried" show item without data" and I added "+0" in the measure but It displays all dates available in dates table.
Ah, your situation is a bit different because of the way you want to do the x-axis. I got it working as follows:
Sales (last n months) = VAR NumOfMonths = - [N Value] VAR ReferenceDate = MAX ( 'Prev Date'[Date] ) VAR StartDate = EOMONTH ( ReferenceDate, NumOfMonths ) VAR Dates = FILTER ( VALUES ( 'Date' ), 'Date'[Date] <= ReferenceDate && 'Date'[Date] > StartDate ) VAR Result = CALCULATE ( SUM ( Sales[Sales] ), Dates ) RETURN IF ( ISEMPTY ( Dates ), BLANK(), Result + 0 )
Anonymous Many times you can have a measure return 0 in these circumstances instead of blank and this fixes the issue.
Greg_Deckler hello, could you please explain more? I didn't get the idea.
Thank you
