Forum Discussion

Mtozero's avatar
Mtozero
Helper I
8 years ago
Solved

Power BI Table In the UI

We have a requirement to display the data in power BI with the below format.    (a) Columns as Rolling 12 months,(show individual month), Previous 12 Months overall total, Current 12 Months and the...
Need Help
freder1ck's avatar
freder1ck
Kudo Kingpin
8 years ago

Here's a pbi file that does most of what you are looking for, Mtozero.

 

Sadly, it looks like my post where I explained how this works is in moderation....

 

Short version:

- disconnected table to setup the context for the report with a couple of calculated columns.

  • -11 to 0 rows are individual months
  • 1 is Trailing Twelve Months; 2 is Trailing Twelve Months last year; 3 is Variance.
  • Variance is the tricky one because it doesn't have just a start and end date.

Measures to get the appropriate amounts.

 

Create a matrix visualization with Range field from the Report table on Columns. 

States or in my case countries on rows.

Put the final measure on as values.

Turn off subtotals and grand totals for rows.

Keep grand totals for columns. 

 

Conditional formatting options are limited right now. 

 

 

 

 

freder1ck's avatar
freder1ck
Kudo Kingpin
8 years ago

You should also set the sort by column for the Range column in the Report table. Sort it by the Value column. This way, the columns in the matrix will line up right.

 

As you dig into this file, please ask whatever questions you need to. 

 

Thanks,

 

Fred