Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BT_19
New Member

Monthly trends and other visualization from weird dataset

Hi Team,

 

I have list of projects in column A and against which corresponding revenue captured for different months in different columns with headers Jan'23, Feb'23 etc (we have 3 years of data - month wise). Now even if I unpivot these columns, it does not help me build report on the same. I need to show the monthly trends and other DAX which will compare previous year to this year etc

1 ACCEPTED SOLUTION

Hi, @BT_19 

 

You can try the following methods. Select the Project Name column, and then select the Transform—Unpivot Other Columns:

vzhangti_2-1695111252602.png

vzhangti_3-1695111276470.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

5 REPLIES 5
WaqarArshad
Helper I
Helper I

You can use the below mquery to transform your data. This format will allow you to create trendlines and lookup YTD, MTD etc. 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PVPJldxACM1FZw6sBRxtjz2TQ7/OPw0jQH2rJ5a/odfr+nXBRYIg6vdLFTL5fvEBD+xXwsnTfQeOSX8LMLWtknefECBrbwlgm1kBV+qqVV9PYIKKXW94Xb+7m8CsKxQQ2PtRC6l5EIJq7yIBTdk+Ps2IrVh2nwiYdB8KCOEicTYjdjBqHliziY3+ZxWjjGKFmB4VSBlGXH74VgVjGTmNHwouXSUD9nkRZMyrNKCvW06xfuCJRv9a9yTHvQSMxx/38fEUj+xq+Y3DyMGN9tvZWStuo5gfj7T6JslKN8fB4mur/W+j31zzk11zxcpdR+cB9QfJMfcqcNC1Ujy86DgslQHH+colRRb96FldR7PR/6264rtpm+eyDs2nezDvhDjXWz6Ptz4XUAmp5eYiewul/dD6rXMBdWVRam70797vVYl1RQ8uDx+Xua4u4kmIdPs4czUpx/ZJ9oRi3e6Thk7a5RH7kwbhoP80Urmyzte18lz651rRICzWRxyWtd/5+afEz26x2VKvlJmtTIdbKfbP/3jf7vv9Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Jan-21" = _t, #"Feb-21" = _t, #"Mar-21" = _t, #"Apr-21" = _t, #"May-21" = _t, #"Jun-21" = _t, #"Jul-21" = _t, #"Aug-21" = _t, #"Sep-21" = _t, #"Oct-21" = _t, #"Nov-21" = _t, #"Dec-21" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Jan-21", Int64.Type}, {"Feb-21", Int64.Type}, {"Mar-21", Int64.Type}, {"Apr-21", Int64.Type}, {"May-21", Int64.Type}, {"Jun-21", Int64.Type}, {"Jul-21", Int64.Type}, {"Aug-21", Int64.Type}, {"Sep-21", Int64.Type}, {"Oct-21", Int64.Type}, {"Nov-21", Int64.Type}, {"Dec-21", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Period"}, {"Value", "Revenue"}})
in
#"Renamed Columns"

Please suggest what action did we take in power query window as this code might work in one file and actual file might be different. Is this system driven code and then we tweaked it or this is entirely needs to be created manually.

BT_19
New Member

Hi,

 

I am unable to attach file here hence table is pasted here.

 

Project NameJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
A $        130,347 $        144,992 $        126,780 $        129,696 $        136,653 $        128,545 $        129,170 $        131,024 $        148,250 $        123,741 $        135,175 $        109,435
B $        141,555 $        118,803 $        104,652 $        110,440 $        113,493 $        118,266 $        125,782 $        133,530 $        103,310 $        109,294 $        127,511 $        108,290
C $        144,032 $        124,811 $        143,935 $        112,997 $        124,308 $        110,716 $        134,737 $        115,277 $        111,987 $        114,607 $        128,718 $        123,068
D $        129,390 $        139,085 $        109,774 $        146,939 $        138,505 $        117,751 $        138,690 $        135,300 $        102,493 $        147,792 $        130,940 $        134,590
E $        121,989 $        131,027 $        106,748 $        116,471 $        138,709 $        126,051 $        149,462 $        135,039 $        142,010 $        125,933 $        130,646 $        109,649
F $        146,102 $        104,579 $        147,849 $        109,609 $        144,029 $        111,267 $        114,766 $        112,459 $        110,382 $        137,761 $        123,494 $        141,871
G $        107,498 $        134,460 $        104,705 $        120,488 $        129,314 $        134,299 $        130,428 $        120,395 $        140,800 $        135,418 $        149,273 $        147,101
H $        100,689 $        133,322 $        119,294 $        105,858 $        125,098 $        140,720 $        123,376 $        100,589 $        100,934 $        101,928 $        141,724 $        148,308

 

Thank you.

Hi, @BT_19 

 

You can try the following methods. Select the Project Name column, and then select the Transform—Unpivot Other Columns:

vzhangti_2-1695111252602.png

vzhangti_3-1695111276470.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

WaqarArshad
Helper I
Helper I

Hi,

 

Would you be able to share some data? 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors