Calculate Month over Month with Month filter

[robertosljunior]

I'd like to know if it's possible to filter a month and preserve the values in a chart.

I have the following sales data:

When I click on the month filter, I would like it to be displayed month by month until the selected month and the rest of the months appear blank.

Would it be possible to make this chart ?

Thanks a lot.

[TomMartens · Accepted solution]

Hey,

this Power BI report contains a report page "Date Axis unrelated", that demonstrates how a slicer can be used to limit the value used on an axis.

Basically it's not the default, slicing (selecting) means filtering - that's the magic of Power BI.

To change this behavior, I use an anrelated dimension, in this example it's either related to fact tables nor to calendar table.

The simple thing

Be aware that I'm using a column from the unrelated table.

Now it's necessary to "map" the data to the colums from the unrelated table, this mapping is necessary for each measure that you want to use with the unrelated column.

For this example this is done by this measure:

show MonthValues Unrelated = 
var maxDateSelection = MAXX(ALLSELECTED('Calendar'),MAX('Calendar'[MonthNoIndex]))
return

// lesser than means values are shown as column
IF(MAX('Calendar Unrelated'[MonthNoIndex]) <= maxDateSelection,
	//map the values to the columns of the
	//unrelated table
	CALCULATE(
		SUM('FactWithDates'[Amount]),
		//using the axis value from the unrelated table to map the facts to the 
		// unrelated table 
		//calendar unrelated -> calendar -> facts
		FILTER(All('Calendar'),
			'Calendar'[MonthNoIndex] = max('Calendar Unrelated'[MonthNoIndex]) &&
			'Calendar'[Year] = max('Calendar'[Year])
		)
	)
)

Hope this helps

[nattran]

Hi @TomMartens ,

Thanks for posting this data file. It's super helpful. I followed your guides but now am having another problem with column total is not displaying. I presented the data in a Matrix table and it shows nothing on the Column total 

Any ideas?

thanks

[robertosljunior]

Hi @TomMartens, I do not know how to express my gratitute towards you for your help. I will never forget your help.

Thanks a lot for your help.

[Anonymous]

Hello @TomMartens 

thank you very much for this measure! It's helping me also a lot! Thanks!!

[TomMartens]

You're welcome! I'm glad I was able to help! 

It was a pleasure to think about your question and put it into my growing (hopefully) pbix file.