showing Quarter based results

[Anonymous]

Hi,

I have vendor data with the spend for each month.I have 12 columns for month.

I need to show each quarter spend for vendor.As months are in columns, i am not sure what is the best way to do this.

Please suggest.

Thanks

[MFelix · Accepted solution]

Hi @Anonymous,

in the query editor unpivot your month columns and you will get two columns with Month and values then calculate your QTD values from there.

Regards,

MFelix

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

If you need to keep the monthly data, please replicate the source table, then, based on the replicated table, you could generate the quarterly data in Query Editor mode.

Select the [Vendor] column, then click Unpivot other columns.

After unpivoting table, you should add a conditional column like below.

Alternatively, you can add a custom column using this code rather than UI operation as above image shows. 

=if List.AnyTrue(List.Transform({"Jan","Feb","Mar"}
,(substring) => [Month]= substring)) 
then "Quarter1" 
else if List.AnyTrue(List.Transform({"Apr","May","Jun"}
,(substring) => [Month]= substring)) 
then "Quarter2" 
else if List.AnyTrue(List.Transform({"Jul","Aug","Sep"}
,(substring) => [Month]=substring)) then "Quarter3" 
else "Quarter4"

Then, please group data records based on vendor and quarter.

Then, pivot table. Select [Quarter] column, click Pivot column, make settings in "Pivot Column" dialog as below.

Final result.

Best regards,
Yuliana Gu

[MFelix · Accepted solution]

Hi @Anonymous,

in the query editor unpivot your month columns and you will get two columns with Month and values then calculate your QTD values from there.

Regards,

MFelix