Pivot table and Time intelligence in Direct Query mode

[marcp2]

Hi

Happy new year every one 🙂

I've a table I want to show in a matrix table

The key points :

• I'm in direct query mode.
• I need to compare data count with the same period of the previous year

My data model is simple :

Date (date time)

Year (integer)

Class (text)

Id (text)

The date field contains only the first day of each month.

I have to classes : Class A and Class B

Measures :

• Result current year=DISTINCTCOUNT (Id)
• Result previous year=CALCULATE(DISTINCTCOUNT (Id),SAMEPERIODLASTYEAR(Date))

I simply need to show table in a matrix (like a pivot table in Excel). Something like this

Year	Month	Class A current year	Class A previous year	Class B current year	Class B previous year
2017		100	95	200	210
	01/01/2017	10	12	12	28
	01/02/2017	8	4	15	13
	...	...	...	...	...

It does not work properly : I got this

Year	Month	Class A current year	Class A previous year	Class B current year	Class B previous year
2017
	01/01/2017	10		12
	01/02/2017	8		15
	...	...	...	...	...

But if I do not show the year, it works :

Month	Class A current year	Class A previous year	Class B current year	Class B previous year
01/01/2017	10	12	12	28
01/02/2017	8	4	15	13
...	...	...	...	...

I know there are limitations in direct query mode 

However question seems simple.

Any idea ?

Thanks for your help

Regards

Marc

[V-lianl-msft · Accepted solution]

Hi @marcp2 ,

I think you would have to create it for yourself by making columns [year], [quarter],[month],[date] and then use them to create a hierarchy.

You can learn about modeling and reporting limitations of direct query connections in this document:

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jthomson]

If it's not something as simple as having subtotals, or some other form of totals, on your matrix turned off, and/or your date hierarchy not working problem, then I don't really know

[marcp2]

Thank you @jthomson for having tried 🙂

Marc