Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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.
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.
Proud to be a Super User!
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
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)))
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
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.
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.
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!
@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.
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.
Proud to be a Super User!
This is a fun one.
OK, we need three tables:
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.
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |