Forum Discussion

sashasneg3's avatar
sashasneg3
Frequent Visitor
8 years ago
Solved

Different time aggregation for different measures within the same table?

Hello,

 

I did achieve the result visually but am very uncomfortable with how I did it: it seems like I've hit a wall in searching for a solution to create the following table: 

 

                            Current Month  | LY same month |   YTD   |  YTD / LYTD  % change  |

Sum (sales)

Count (clients)

measure 3

measure 4

...5

...6

...7

 

 

Challenges: 

 

1. For 2 columns with months I created a Table with measures in rows and I am using my Date, then I have to filter on 2 months of interest. Ideally I would want the most recent month to show, as well as the same ``most recent month`` from last year - automatically, data is refreshed monthly

2. For YTD column I had to create a new table and place it next to the first table, because I can`t find a way to have 2 months AND YTD as table columns at the same time. Ideally - it should be just a column within Table 1, not a separate column

3. Finally, the last column are in fact KPI cards mimicking table cells as each of them is a measure itself. ( I am using time intelligence to create YTD/LYTD % change, for each of the measures in Column 1, for example: Appr Avg YoY = [Appr Avg YTD] / calculate([Appr Avg YTD], dateadd(Table[Date], -1, YEAR)) -1). Sorry I can't share the data it is sensitive.

 

So the big question is: can we show 2 separate months, YTD and YOY for MEASURES as rows? 

 

Are there any other workarounds or more elegant ideas you might suggest? Would appreciate any comments.

 

Thank you,

Aleksandra

  • sashasneg3,

     

    Hi Aleksandra,

     

    There are measures in the rows and columns in your example. Measures are dynamic. So you can't a visual that is exactly the same with your example. Maybe you can try it like this.

    1. The column could be dates from a column of one table.

    2. The visualization is a Matrix.

    3. The Columns field is date.

    4. The Value field is the results you want.

    5. Enable "Show on rows" in the settings of Matrix.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

     

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    sashasneg3,

     

    Hi Aleksandra,

     

    There are measures in the rows and columns in your example. Measures are dynamic. So you can't a visual that is exactly the same with your example. Maybe you can try it like this.

    1. The column could be dates from a column of one table.

    2. The visualization is a Matrix.

    3. The Columns field is date.

    4. The Value field is the results you want.

    5. Enable "Show on rows" in the settings of Matrix.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale