Show current MTD totals when Months are column names

[Anonymous]

I want to show the current MTD in a scorecard.  When I get data for the next, I need the MTD scorecard to change to the next month(Apr would change to May for this example).   

I need help, cause I am unsure how the set current MTD when the month names are column names as totals.

Company	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL YTD
Business 1	$429,614	$476,583	$704,145	$809,477									$2,419,819
Business 2	$6,137,508	$6,307,403	$8,153,579	$9,227,444									$29,825,935
Business 3	$1,104,687	$1,263,653	$1,582,506	$1,677,829									$5,628,675
Business 4	$940,182	$890,088	$1,369,215	$1,792,425									$4,991,910
Business 5	$4,483,125	$5,160,110	$6,471,902	$7,892,766									$24,007,903
Business 6	$6,072,445	$7,209,537	$9,417,692	$10,606,649									$33,306,324
Business 7	$1,352,037	$1,177,437	$1,808,468	$1,555,193									$5,893,135
Business 8	$7,155,104	$7,988,619	$10,222,523	$12,203,791									$37,570,036
Business 9	$642,613	$725,616	$823,462	$908,350									$3,100,041
Business 10	$2,010,013	$1,667,475	$1,974,454	$2,603,173									$8,255,114
Business 11	$211,980	$322,336	$529,127	$517,740									$1,581,183
Business 12	$10,750	$10,946	$21,290	$23,442									$66,427
Business 13	$182,628	$168,755	$107,720	$115,922									$575,025
TOTALS	$30,732,685	$33,368,557	$43,186,085	$49,934,200									$157,221,527

[SamWiseOwl]

Hi @Anonymous 

In the Query Editor (Home -> Transofmr data), remove Totals from the Company:

Remove the last column

Highlight all the column you want to unpivot and choose Unpivot only selected columns

That would look like this

Remove the $ from the data

Change the data type from text to decimal number 

Create a new table with the months and Sort order by clicking Enter Data on the Home tab:

MonthsSort order

Jan	1
Feb	2
Mar	3
Apr	4
May	5
Jun	6
Jul	7
Aug	8
Sep	9
Oct	10
Nov	11
Dec	12

Click close and apply in the top right to save the changes.
Click on the month column and tell it to sort by the sort order:

Join this table onto your Month value table using the months:

Use the Months from the Month sort table as your Columns:

Choose show items with no data on Months:

Create a card visual with Value in it:

Apply a filter using the Filter pane to return the MAX month:

Then enjoy the end result!

[SamWiseOwl · Accepted solution]

Hi @Anonymous 

If you can't unpivot then consider a measure like:

Return Non Blank = COALESCE( Sum('Coalesce Table'[April]), sum('Coalesce Table'[March]),sum('Coalesce Table'[Feb]), sum('Coalesce Table'[Jan]))
This returns the first non blank value.

[SamWiseOwl]

Ahh sometimes the simplar solution is the best one!

I appreciate the solution and Kudos thank you 🙂

[SamWiseOwl]

Hi @Anonymous 

Would it be possible to unpivot the data in the Query Editor.

This would make all the Month Names in one column and all the values in another.

[Anonymous]

Can you please show me an example with the data provided?