cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gl
Frequent Visitor

Setting a bar chart to a default selection

Hi All.

 

So, I’ve got a basic dataset containing a couple of columns along with a date column which spans back from 2011 to present.

 

I’m trying to create a bar graph which defaults to showing just the previous month’s data when the date is completely unfiltered. However, when a filter is selected (i.e. another year or month) the graph alters to show the filtered selection. Then, when the filter is removed, the graph returns to showing just the previous month. Is this possible?

 

Essentially I’m trying to replicate a graph from another source which does this perfectly.

 

I’ve been Googling and have found various functions which may appear to help me (ISFILTERED, HASONEFILTER) but I can’t manage to get anything to work as I want it. It's also quite difficult to find the right phrase to Google.

 

So far all I’ve managed to achieve is a new calculated column which states if the date falls in the previous month or not (this uses the EOM function). This allows me to create a graph just showing that period, but then when filters are made outside of that month the graph obviously turns blank.

 

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

@gl If you're trying for my solution, you'll also need the third disconnected months table. I don't think @Vvelarde's suggestion will work if you need to chart multiple months.

 

That formula has a check for IF(HASONEVALUE(DateTable[Month])... and on a chart with months on the x-axis, each month has one value for month, so it will always ignore that slicer rule on the chart.

 

Now, that being said my solution won't work on a chart with multiple months for the same reason. From your original description I thought you only ever wanted to show one month at a time: either last month by default, or a selected month in a slicer. But there's a way around it.

 

  1. Do everything in my original solution except the Monthly Sales measure itself. I'm about to give a new formula for that. But first,
  2. Create an inactive relationship between DateTable[Month of Year] and Months[Month of Year]. It has to be inactive because we only want to use it part of the time.
  3. Figure out what the maximum number of months you want to graph should be. Now that I've reread your original post I see that you wanted to select multiple months or even a whole year, rather than selecting a single month like I thought you wanted. In my example I'm setting the threshold at 24. That means you can see a graph with one month or multiple months, up to 2 full years. Any more than that and it will default back to last month only. I can't think of a good way around this limitation, sorry. You can set the threshold as large as you want but it can't be every month possible. Selecting every month is the same as selecting no months in the slicer, and we want no selection to default back to last month. So you just have to pick a number that works for your personal situation. My formula uses 24 because in my experience looking at more than 2 years worth of monthly data starts to look pretty messy, and you should probably set up a different measure and chart for that sort of thing.
  4. That's it. Except the new formula...
Monthly Sales = IF(
	HASONEVALUE(Months[Month of Year]),
	CALCULATE(
		SUM(MockSales[Amount]),
		USERELATIONSHIP(
			Months[Month of Year],
			DateTable[Month of Year]
		)
	),
	IF(
		COUNTROWS(Months) > 24,
		CALCULATE(
			SUM(MockSales[Amount]),
			FILTER(
				DateTable,
				DateTable[MonthDiff] = -1
			)
		),
		CALCULATE(
			SUM(MockSales[Amount]),
			USERELATIONSHIP(
				Months[Month of Year],
				DateTable[Month of Year]
			)
		)
	)
)

If you need that translated back to English:

 

Monthly Sales = If I've selected 1 month in the Months slicer, then use the slicer selection to filter the DateTable,
else if I've selected 0 or more than 24 months in the Months slicer, then give me just last month's sales total,
else if I've selected more than 1 but less than or equal to 24 months in the Months Slicer, then use the slicer selection to filter the DateTable.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
v-ljerr-msft
Microsoft
Microsoft

@gl

 

According to your description, what you are trying to do is something like just setting a default value for your Slicer to previous month, so that your bar graph will default to show the previous month's data in this scenario, right?

 

However, it is not possible to do it currently. So in addition to the solutions provided above, I also suggest you to vote this similar idea here.

 

Regards

Vvelarde
Community Champion
Community Champion

@gl

 

An alternative solution is:

 

a) A Date Table, with Date, year , month and other columns that you need. 

b) Related To your table using Date column

c) use 2 slicers: Year and Month.

d) create a measure with this dax:

 

SalesTotal = if(HASONEVALUE(Calendario[Month]),CALCULATE(SUM(Sales[Sales])),CALCULATE(Sum(Sales[Sales]),DATEADD(LASTDATE(Sales[Date]),-1;MONTH)))




Lima - Peru
gl
Frequent Visitor

@Vvelarde

 

Thanks for your reply... however i'm having a bit of an issue when I try this. I've created the [Date Table] which contains the following fields

 

Capture.PNG

 

I've added the two slicers from this table, and also created the relationship between this and the main table. However when nothing is selected all of the months are still showing. I'm using the field 'MonthYear' from the above table for the axis.

 

Capture.PNG

This is the DAX measure i'm using...

 

Measure = if(HASONEVALUE('Date Table'[Month]),CALCULATE(Count('Total Calls'[Call Ref])),CALCULATE(Count('Total Calls'[Call Ref]),DATEADD(LASTDATE('Total Calls'[Date]),-1,MONTH)))

 

For reference, this is the contents of the 'Total Calls' table.

 

Capture.PNG

 

Any ideas?? I'm really pleased that there is a solution to this issue though as there are quite a few other graphs I can replicate this way too. And i've voted for a solution in the suggestions forum.

 

Thanks for all your help so far! 

KHorseman
Community Champion
Community Champion

@gl If you're trying for my solution, you'll also need the third disconnected months table. I don't think @Vvelarde's suggestion will work if you need to chart multiple months.

 

That formula has a check for IF(HASONEVALUE(DateTable[Month])... and on a chart with months on the x-axis, each month has one value for month, so it will always ignore that slicer rule on the chart.

 

Now, that being said my solution won't work on a chart with multiple months for the same reason. From your original description I thought you only ever wanted to show one month at a time: either last month by default, or a selected month in a slicer. But there's a way around it.

 

  1. Do everything in my original solution except the Monthly Sales measure itself. I'm about to give a new formula for that. But first,
  2. Create an inactive relationship between DateTable[Month of Year] and Months[Month of Year]. It has to be inactive because we only want to use it part of the time.
  3. Figure out what the maximum number of months you want to graph should be. Now that I've reread your original post I see that you wanted to select multiple months or even a whole year, rather than selecting a single month like I thought you wanted. In my example I'm setting the threshold at 24. That means you can see a graph with one month or multiple months, up to 2 full years. Any more than that and it will default back to last month only. I can't think of a good way around this limitation, sorry. You can set the threshold as large as you want but it can't be every month possible. Selecting every month is the same as selecting no months in the slicer, and we want no selection to default back to last month. So you just have to pick a number that works for your personal situation. My formula uses 24 because in my experience looking at more than 2 years worth of monthly data starts to look pretty messy, and you should probably set up a different measure and chart for that sort of thing.
  4. That's it. Except the new formula...
Monthly Sales = IF(
	HASONEVALUE(Months[Month of Year]),
	CALCULATE(
		SUM(MockSales[Amount]),
		USERELATIONSHIP(
			Months[Month of Year],
			DateTable[Month of Year]
		)
	),
	IF(
		COUNTROWS(Months) > 24,
		CALCULATE(
			SUM(MockSales[Amount]),
			FILTER(
				DateTable,
				DateTable[MonthDiff] = -1
			)
		),
		CALCULATE(
			SUM(MockSales[Amount]),
			USERELATIONSHIP(
				Months[Month of Year],
				DateTable[Month of Year]
			)
		)
	)
)

If you need that translated back to English:

 

Monthly Sales = If I've selected 1 month in the Months slicer, then use the slicer selection to filter the DateTable,
else if I've selected 0 or more than 24 months in the Months slicer, then give me just last month's sales total,
else if I've selected more than 1 but less than or equal to 24 months in the Months Slicer, then use the slicer selection to filter the DateTable.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




gl
Frequent Visitor

@KHorseman Absolutely perfect! Thanks a lot for your help.

KHorseman
Community Champion
Community Champion

This is a fun one.

 

OK, we need three tables:

  1. your data table. I'm going to pretend this is a sales dataset. My table is called MockSales. It has three columns: date, sales rep, and amount.
  2. a date table. There is an active relationship between DateTable[Date] and MockSales[Date]. In addition to the fairly standard stuff, mine has a couple of columns that make this easier. If you don't have these and can't figure out how to make them let me know:
    • Month of Year - the month and year formatted as "Sep 2016" etc
    • MonthIndex - sequential whole numbers starting at 1 and increasing by 1 each month in the table. Used as the sort order for Month of Year and as the basis of the math used to derive...
    • MonthDiff - sequential integers. The current month is 0. Last month is -1. Next month is 1. Et cetera.
  3. A months table. It is just distinct values of Month of Year and MonthIndex. There are no relationships between this table and any other. That's important. Add a new table, and the DAX formula is Months = SUMMARIZE(DateTable, DateTable[Month of Year], DateTable[MonthIndex])

 

The regular Sales Amount formula is of course simply SUM(MockSales[Amount]). This can be charted against any column in the date table to see sales broken down by that period. Month of Year, for instance.

 

Add a slicer using Months[Month of Year] to your page. Leave everything unselected on it. Then add this measure:

 

Monthly Sales = IF(
	HASONEVALUE(Months[Month of Year]),
	CALCULATE(
		SUM(MockSales[Amount]),
		FILTER(
			DateTable,
			DateTable[Month of Year] = FIRSTNONBLANK(Months[Month of Year], 1)
		)
	),
	CALCULATE(
		SUM(MockSales[Amount]),
		FILTER(
			DateTable,
			DateTable[MonthDiff] = -1
		)
	)
)

As long as nothing is selected in the slicer, it will show only last month. If anything is selected, it will show that month's sales.

 

NoSelection.PNG

 

WithSelection.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors