Forum Discussion
How to show completed months
I have a Year slicer where you can select the different years. I also have a table which shows the months/year / sales
When I select a year i want it to show me all months and all sales, even if the month didn't have a sale in, however atm I am only getting the months that have a sale in. I added a +0 to my measure but when I do this and select current year it gives me future months too. Example below, I selected 2022 I want it to show me jan, feb and march but its only showing Jan and March.
Hi Anonymous
You need create a Calendar Date table that contains all the months .Then compare the Calendar Date table with original data table .If the month is not in original data table , return 0 .If the month is in original data table ,return the value from original table .I created a sample , you can have a look .
(1)Create a Calendar Date table with column [Year] [Month] [Month No] , add a slicer with column ‘Calendar Date’ [Year]
Calendar Date = DISTINCT( SELECTCOLUMNS( CALENDAR(DATE(2021,01,01),DATE(2022,12,31)),"Year",YEAR([Date]), "Month",FORMAT([Date],"mmmm"),"Month Number", MONTH([Date])))You will get a result like this :
(2)Create a measure to compare these two tables to return the value or 0 .
Measure = CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Year]=MAX('Calendar Date'[Year]) && [Month]=MAX('Calendar Date'[Month])))+0(3)Sort by month
The final result is as shown below .
I have attached my pbix file , you can refer to it .
3 Replies
- dhruvinushah
Responsive Resident
RIght click on the field in the Visualizations pane, select "Show Items with no data"
This should fix your problem. Hi,
as dhruvinushah said you need to flag "Show Items with no data" but by doing that it will sowh also future months if present in the data dimension.
What you could do is to create a calculated column on the date calendar:IsClosedMonth = IF(FORMAT(TODAY(), "yyyyMM") > FORMAT('Date'[Date], "yyyyMM"),
"Closed",
"Not Closed"
)Once the calculated column has been added use it as a filter on the visual:
Hope this is what you are looking for.
Hi Anonymous
You need create a Calendar Date table that contains all the months .Then compare the Calendar Date table with original data table .If the month is not in original data table , return 0 .If the month is in original data table ,return the value from original table .I created a sample , you can have a look .
(1)Create a Calendar Date table with column [Year] [Month] [Month No] , add a slicer with column ‘Calendar Date’ [Year]
Calendar Date = DISTINCT( SELECTCOLUMNS( CALENDAR(DATE(2021,01,01),DATE(2022,12,31)),"Year",YEAR([Date]), "Month",FORMAT([Date],"mmmm"),"Month Number", MONTH([Date])))You will get a result like this :
(2)Create a measure to compare these two tables to return the value or 0 .
Measure = CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Year]=MAX('Calendar Date'[Year]) && [Month]=MAX('Calendar Date'[Month])))+0(3)Sort by month
The final result is as shown below .
I have attached my pbix file , you can refer to it .
