Graphic visual with Moving Annual Total values

[Anonymous]

Hi!

I'm trying to make a report that has a graphic visual to show an evolution of moving annual total sales for 5 years. I made 5 measures, one for each year.

In the report, I have a date selector that references a master table with dates. This master table is related to the facts table and whas used to perform the 5 moving annual total measures.

So, in the graphic visual I put the Date as axis and the 5 measures as values. The problem that I have, is that the 5 columns and together and shown all in the same year.

How can I make it to show seperate columns for each measure and by its respective year?

The main measure is this:

Ventas MAT = 
SWITCH(
	VALUES(
		'Selector Medida'[Medida]
	);
	"USD";
	CALCULATE(
		DIVIDE(
			SUM(
				'Ventas Mensuales'[USD]
			);
			1000
		); 
		DATESINPERIOD(
			'Período a Analizar'[Fecha]; 
			LASTDATE(
				'Período a Analizar'[Fecha]
			); 
			-1; 
			YEAR
		)
	);
	"UNSTD";
	CALCULATE(
		DIVIDE(
			SUM(
				'Ventas Mensuales'[UNSTD]
			);
			1000
		); 
		DATESINPERIOD(
			'Período a Analizar'[Fecha]; 
			LASTDATE(
				'Período a Analizar'[Fecha]
			); 
			-1; 
			YEAR
		)
	)
)

And for every previous year I made this:

Ventas MAT Año-1 = 
CALCULATE(
	'Medidas VentasMPh'[Ventas MAT];
	DATESINPERIOD(
		'Período a Analizar'[Fecha]; 
		LASTDATE(
			DATEADD(
				'Período a Analizar'[Fecha];
				-1;
				YEAR
			)
		); 
		-1; 
		YEAR
	)
)

And this is how the report looks like:

Thanks in advance!

[Anonymous]

@Anonymous,

Firstly, you can follow the guide in the blogs below to create a Moving Annual Total measure.

https://www.microsoftpressstore.com/articles/article.aspx?p=2228441&seqNum=2
http://www.dash-intel.com/powerbi/modeling_date_functions.php

Secondly, create a new table using DAX below. Please note that there is no relationship between the new table and your original table.

Date = ADDCOLUMNS(CALENDAR(DATE(2010,1,1),DATE(2017,12,30)),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",FORMAT([Date],"YYYYMM"))

Thirdly, create a measure in your original table, drag the measure to the visual level filter of your bar chart and set its value to “not blank”

checkmeasure =   IF(MAX(YourOriginalTable[Year])>(MAX('Date'[Year])-5)&&MAX(YourOriginalTable[Year])<=MAX('Date'[Year]),IF(MAX(YourOriginalTable[Month])=MAX('Date'[Month]),1,BLANK()),BLANK())

At last, create a slicer using the YearMonth column of the new Date table, then use it to filter your bar chart.

Regards,
Lydia