Data being excluded

[Anonymous]

Good monday to everyone!

I'm having trouble with some data. In my model, what I have is a table that contains sales from an SQL Server, a other table that contains more sales from an Excel archive. What I did was a "merge query" to have all sales in one table.

Know I did a SUMMARIZE to filter a bit the original table and have less rows for specific calculations. The problem that I have is that in the summarized table I'm loseing data from the table that came from the Excel archive.

I have in a page report a filter for the dates, with a selection from 01/01/16 to 12/01/16 to have all 2016. In same page I have to table visuals. One with sales of current period and sales of same period last year of the original table, an the other with same columns but from the summaried table. In the table that was made from an Excel archive, for 2016 I have sales from 01/01/16 to 10/01/16. So, as result, in the summarized table I get that the sales of last year is excluding the sales of that last month selected despite it has sales.

To clarify:
Date Filter: 01/01/16 - 12/01/16

Data in table from Excel: 01/01/15 - 12/01/16 and 01/01/16 - 10/01/16

Data in table from SQL Server: 01/01/15 - 12/01/16 and 01/01/16 - 12/01/16

In the table visual, in the column of sales same period last year, sales of months 10 and 11 of year 2015 are being excluded. I can't figure out why.

Summarized tableOriginal table (SQL Server table + Excel table)

Here is the SUMMARIZE code:

Ventas Zona-Gestión = 
SUMMARIZE(
	'Ventas eLogistica';
	'Ventas eLogistica'[Fecha];
	'Ventas eLogistica'[IdZonaGestion];
	'Ventas eLogistica'[IdPais];
	'Ventas eLogistica'[IdAreaTerapeutica];
	'Ventas eLogistica'[IdLineaComercial];
	'Ventas eLogistica'[IdFamilia];
	'Ventas eLogistica'[IdCanalMPh];
	"USD";
	DIVIDE(
		SUM(
			'Ventas eLogistica'[USD]
		);
		1000
	);
	"USDAA";
	CALCULATE(
		DIVIDE(		
			SUM(
				'Ventas eLogistica'[USD]
			);
			1000
		); 
		SAMEPERIODLASTYEAR(
			'Ventas eLogistica'[Fecha]
		)
	);
	"ML";
	DIVIDE(
		SUM(
			'Ventas eLogistica'[ML]
		);
		1000
	);
	"MLAA";
	CALCULATE(
		DIVIDE(		
			SUM(
				'Ventas eLogistica'[ML]
			);
			1000
		); 
		SAMEPERIODLASTYEAR(
			'Ventas eLogistica'[Fecha]
		)
	);
	"UNSTD";
	DIVIDE(
		SUM(
			'Ventas eLogistica'[UNSTD]
		);
		1000
	);
	"UNSTDAA";
	CALCULATE(
		DIVIDE(		
			SUM(
				'Ventas eLogistica'[UNSTD]
			);
			1000
		); 
		SAMEPERIODLASTYEAR(
			'Ventas eLogistica'[Fecha]
		)
	);
	"UNSTDAAx2";
	CALCULATE(
		DIVIDE(		
			SUM(
				'Ventas eLogistica'[UNSTD]
			);
			1000
		); 
		DATEADD(
			'Ventas eLogistica'[Fecha];
			-2;
			YEAR
		)
	)
)

Can someone please kindly help?

Thanks in advance.

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

From your description, it seems data in table from Excel contains data from 01/01/15 to 10/01/16, while data in table from SQL Server contains data from 01/01/15 to 10/01/16. You merge those two tables use Merge Queries feature in desktop and you want to display YTD and YTD in last year same period, right?

In your scenario, you can create a calendar table from the merged table:

Date =
CALENDAR (
    EOMONTH ( MIN ( 'Merge1'[Date] ), -1 ) + 1,
    EOMONTH ( MAX ('Merge1'[Date] ), 0 )
)

As the SQL table has the consecutive date column, you can build a relationship between the calendar table and merged table use this date column. Then create measures below:

YTD for archive =
IF (
    MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( 'Merge1'[Date] ), ALL ( 'Merge1' ) ),
    CALCULATE ( SUM ( 'Merge1'[Archive.Value] ), DATESYTD ( 'Date'[Date] ) )
)

YTD LY for archive =
    CALCULATE (
        [YTD for archive],
        SAMEPERIODLASTYEAR (VALUES ( 'Date'[Date] )
                ))

You can download attached .pbix to have a look.

Reference:
YTD last year DAX

Best Regards,
Qiuyun Yu

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

From your description, it seems data in table from Excel contains data from 01/01/15 to 10/01/16, while data in table from SQL Server contains data from 01/01/15 to 10/01/16. You merge those two tables use Merge Queries feature in desktop and you want to display YTD and YTD in last year same period, right?

In your scenario, you can create a calendar table from the merged table:

Date =
CALENDAR (
    EOMONTH ( MIN ( 'Merge1'[Date] ), -1 ) + 1,
    EOMONTH ( MAX ('Merge1'[Date] ), 0 )
)

As the SQL table has the consecutive date column, you can build a relationship between the calendar table and merged table use this date column. Then create measures below:

YTD for archive =
IF (
    MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( 'Merge1'[Date] ), ALL ( 'Merge1' ) ),
    CALCULATE ( SUM ( 'Merge1'[Archive.Value] ), DATESYTD ( 'Date'[Date] ) )
)

YTD LY for archive =
    CALCULATE (
        [YTD for archive],
        SAMEPERIODLASTYEAR (VALUES ( 'Date'[Date] )
                ))

You can download attached .pbix to have a look.

Reference:
YTD last year DAX

Best Regards,
Qiuyun Yu

[Anonymous · Accepted solution]

Thanks @v-qiuyu-msft for your reply.

It helped me to work better with dates. I had created before a table with the dates for source, but not as calendar. About YTD and YTD LY, I had the idea of making them as part of the SUMMARIZED table columns and not as measures. Just as a personal decision.

Anyway, thanks a lot for your help. It clarify a lot on how to work with dates.

Have a great day!

[v-qiuyu-msft]

Hi @Anonymous,

Was your issue solved now? Was my suggestion helpful to you? If the issue is gone, would you please mark a helpful reply so that we can close the thread?

Best Regards,
Qiuyun Yu

[Anonymous]

Hi @v-qiuyu-msft.

Yes, your suggestions help me a lot. With your tips and adding a bit of work on the report, I was able to work it out.

Thanks a lot.