Graphing "Show last 12 months" + "Show this month"

[GarlonYau]

Hello fellow PowerBI Enthusiats!

I believe I have a rather straight forward graphing question, but I just can't seem to solve it on my own.

For example:

Let's pretend I'm looking at sales data and I'm trying to plot it on a stacked column chart where the x axis is the date, and the y axis is sales dollars, and today is August 16, 2019.

• "Show last 12 calendar months" - this will only show sales data from Aug 1, 2018 to July 31, 2019.
• "Show last 12 months" - this will only show sales data from Aug 16, 2018 to Aug 16, 2019
• "Show this month" - this will only show sales data from Aug 1, 2019 - Aug 16, 2019.

Is there a way to get the graph to "Show last 12 calendar months" + "Show this month"?

Effectively that would be Aug 1, 2018 to Aug 16, 2019. 

Am I missing somthing here? Is there actually a way to combine these filters to show what I want on the graph? If there isn't, are there any work around solutions out there?

Cheers,

Garlon

[Ashish_Mathur]

Hi,

I'm not sure how much but see if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

This should atleast get you previous x months.  Getting the revenue for the current month should not be difficult.

If this does not help, then please share sample data (which can be pasted in an Excel file).

[GarlonYau]

Hmm... Unfortuately I need it to be in a graph rather than a pivot table.

[Ashish_Mathur]

Hi,

That should be simple.  Once the numbers are functionality work as expected, building a visual is as simple as cliking on the icon of your desired visual.

[KHorseman]

Well if all you wanted was the option to switch between last 12 calendar months and this month only, this would be very easy. Last 12 calendar months and last 12 months makes it a little harder.

Any solution I can think of will require a small helper table. Add a table that has your three options as rows (use the Enter Data button). We'll call this table MeasureBehavior, and the column will be called "Show."

Show

Last 12 Calendar Months

Last 12 Months

This Month

This table can just sit in your data model. Don't create any relationships. It's just there to supply us with some text options. In the end you'll put this column into a slicer on your report page. The user can then select one of these options and it will affect the measure we'll write at the end of this. But first a little more setup.

I'm going to assume you have a date dimension table in your data model. The x-axis of your graph is then the DimDate[Date] column, right? If you're doing anything graphing by dates you really should have a date table. If you already have one, this will be easier if you add a MonthDiff column to it:

MonthDiff = DATEDIFF(TODAY(), DimDate[Date], MONTH)

I find MonthDiff, WeekDiff and DayDiff columns endlessly useful for setting relative filters on reports, not just referencing in measures as we're going to do. Also occasionally YearDiff. Technically I think you can get away with substituting the above code into my measure below wherever you see the MonthDiff column referenced, but it will run faster if you have the column. Same deal with a DayDiff column:

DayDiff = DATEDIFF(TODAY(), DimDate[Date], DAY)

Once you have those two columns on your date table, as well as creating the MeasureBehavior table, you just need to add the actual measure that brings it all together. The way I've written this measure, your graph will be blank if no single option is selected in the Show slicer. If you don't want it to do that, you can substitute something else in the line at the bottom that says BLANK().

Sales Amount = VAR a = IF(
	HASONEVALUE(MeasureBehavior[Show]),
	FIRSTNONBLANK(MeasureBehavior[Show], 1),
	BLANK()
)
RETURN SWITCH( a,
	"Last 12 Calendar Months",
	CALCULATE(
		SUM(SalesTable[SalesDollars]),
		FILTER(
			DimDate,
			DimDate[MonthDiff] >= -12
			&& DimDate[MonthDiff] <= 0
		)
	),
	"Last 12 Months",
	CALCULATE(
		SUM(SalesTable[SalesDollars]),
		FILTER(
			DimDate,
			DimDate[DayDiff] >= -365
			&& DimDate[Date] <= TODAY() //or you could write this line as: && DimDate[DayDiff] <= 0
		)
	),
	"This Month",
	CALCULATE(
		SUM(SalesTable[SalesDollars]),
		FILTER(
			DimDate,
			DimDate[MonthDiff] = 0
		)
	),
	BLANK()
)

[GarlonYau]

Thanks for the reply!

I'm not exactly looking to have a slicer to switch between them.  If that was the case, creating a bookmark with the appropriate buttons would have sufficed.

I'm actually trying have one graph that shows the last 12 "completed" calendar months in addition to whatever is in the current month. I need to have it all in one graph as this will get pulled into a dashboard once it gets pushed to the PowerBI Service.