Using views or tables and unpivot data

[SantosLuis]

New to Power BI... doing an online course but thus far didn't cover this specific work requirement:

Need to consume financial data from a SQL Server database view. View contains financial budgets and actuals for each financial year (FY), GL account and each row includes 12 financial periods (i.e. months) all in a single row (example below). 

Also in Australia FY's go from July to June so for FY 2021 period 1 is June 2020 and period 12 is June 2021.

lgr_acc	pst_yer	act_p01	act_p02	act_p03	act_p04		bgt_p01	bgt_p02	bgt_p03
1004	2021	-241	-872	-908	-2338.82	(...)	-899	-1798	-2697

I have two questions towards a better understanding of Power BI recommendations and best practices. 

1. is that OK to use a database view as it already includes GL account description, funding source description, etc OR should I use  source tables instead so that I can have separate lookup tables for filtering, drilling, etc.

2. All our financial tables include 12 financial year periods so for financial year 2021, actuals for period 1 are stored in field act_p01 (July 2020) and budget for period 12 is in field bgt_p12. For reporting purposes I think (?) I need to derive a period number (at present determined by the field name) and unpivot data so I can filters, present graphs, etc in a vertical fashion. I envisage data to be then consumed as per format below.

lgr_acc	pst_yer	cmt_bal	period	actuals	budget
1004	2021	0	1	-241	300
1004	2021	0	2	-50	100
1004	2021	0	3	-5000	6000

Any suggestions, guidance and pointers to relevant online information will be most appreciated.

Thank you

[v-xiaoyan-msft]

Hi@SantosLuis，

Has your problem been solved ? if so, please consider Accept a correct reply as the solution to help others find it.

Best regards,

Caitlyn Yan

[SantosLuis]

Amitchandak,

Thank you, very useful and complete answer.

Regards

[amitchandak · Accepted solution]

@SantosLuis , I feel like I have seen this database.

Steps

1. Unpivot the period columns - https://radacad.com/pivot-and-unpivot-with-power-bi
2. Split the column by _  . You will get like act P06  -https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

3. Combine/Concat year and period or remove p from the period(and convert to number)  and use [Year]*100 + [Priod] - https://docs.microsoft.com/en-us/powerquery-m/text-combine

https://docs.microsoft.com/en-us/powerquery-m/text-remove

Now create a separate table with year, period, and year period (combine)

Create an rank and use for period over period analysis

new column

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

measure
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA