Get a summary table from transaction table

[JasMack]

Hi everyone,

I am new here.

I have a table which looks like this.

Team	Person	Period	Cost	Revenue
A	Tom	1	100	200
A	Tom	2	150	100
A	Jack	1	50	90
A	Jack	2	75	80
B	Sarah	1	300	400
B	Sarah	2	150	500

Can you please help me with the achieving the following results on Power BI desktop using the table above?

Particular	Prior Month (Period <2)	Year to date
Cost	450	825
Revenue	690	1370

Thanks.

Jason

[Greg_Deckler · Accepted solution]

@JasMack Create a disconnected table like this:

Particulars = { "Cost", "Revenue" }

Create these measures:

Revenue YTD = SUM('Table'[Revenue])

Cost YTD = SUM('Table'[Cost])

Revenue Prior = SUMX(FILTER('Table',[Period] = 1), [Revenue])

Cost Prior = SUMX(FILTER('Table', [Period] = 1), [Cost])

Prior Month (Period < 2) = IF(MAX('Particulars'[Column1]) = "Cost", [Cost Prior], [Revenue Prior])

Year to Date = IF(MAX('Particulars'[Column1]) = "Cost", [Cost YTD], [Revenue YTD])

Put Column1 from Particulars in your table visual along with the last 2 measures.

[JasMack]

Many thanks @Greg_Deckler .

But I have 2 more questions.

1. My result table automatically gets sorted alphabetically. Is there a way to stop that. For example: I would prefer to have "Revenue" instead of "Cost" as first row.

Particular	Prior Month (Period <2)	Year to date
Cost	450	825
Revenue	690	1370

2. Suppose I need to have put the values in row revenue in percentage format. Is it possible?

Thank you again.

Jason