Forum Discussion
Pivot table and Time intelligence in Direct Query mode
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
- Hi Anonymous ,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:Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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
Thank you jthomson for having tried 🙂
Marc
- Hi Anonymous ,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:Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
