How to calculate sum of Month detail based on YTD Data?

[C0unt]

Hello everyone,

I'm fairly new to DAX but I am struggling to determine a solution. Here is my scenario. The data I have is Year to Date data, meaning February's data is the sum of February purchases and January purchases. I would like to generate a visual that shows the month only increase in purchases (in total, doesn't matter by city) which can be filtered by month.

Data:

Table1
City	Month	YTD Purchases ($)
Chicago	01	$                           100
New York	01	$                           200
Boston	01	$                           100
Chicago	02	$                           250
New York	02	$                           200
Boston	02	$                           150
LA	02	$                           100
Chicago	03	$                           350
New York	03	$                           200
Boston	03	$                           150
LA	03	$                           150
Seattle	03	$                           150

Output:

If Filter is:	Visual: Total Month Increase In Purchases	Calculation
01	$                                                                            400	(400-0)
02	$                                                                            300	(700-400)
03	$                                                                            300	(1000-700)

I attempted to create a measure but the month -1 does not seem to be working.

Month Increase =
SUMX(ALL(Table1),CALCULATE(SUM(Table1[Purchases],INT[Month]) -
SUMX(ALL(Table1),CALCUALTE(SUM(Table1[Purchases],INT[Month]-1)

I also tried creating a variable for each month, but when I filter on a month, all other variables recalculate to 0. Any help would be greatly appreciated!

[dkay84_PowerBI]

I was able to solve this using the query editor to create a custom column.  However, while the preview shows the data correctly, I am getting a cyclic error that prevents the query from loading.  Maybe give it a try and others here can chime in as to why it may be throwing this error.

 

Note that my source was a table where I hard coded the values as per your example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7ITE5Mz1fSUTIEYQMDpVidaCW/1HKFyPyibKiwEVTYKb+4JD8PTS3CBCMQNsUwwQibCSBBQ6haH0eYAIaRxiCMaaQxNiON0Y1EFghOTSwpyUlFFo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [City = _t, Month = _t, #"YTD Purchases ($)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Month", Int64.Type}, {"YTD Purchases ($)", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"YTD Sales", each List.Sum([#"YTD Purchases ($)"]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"YTD Sales", "YTD Sales"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Sales Increase", each try Query1[YTD Sales]{[Index]}-Query1[YTD Sales]{[Index]-1} otherwise Query1[YTD Sales]{[Index]})
in
    #"Added Custom"

Assuming you can get this query to load, you would simply use the "Sales Increase" field for your chart.

[dkay84_PowerBI]

I'm not sure why this cyclic reference error is occuring, as I have used this technique in the past with no problems.  I learned of this technique from a widely referenced Power Query book, "M is for Data Monkey."  However, I did seem to solve the problem with a slight workaround.

If I extract the list of months as a new query and remove duplicates, convert to a table, and then add a custom column to the new table as described before (making sure to reference the original query) this seems to prevent whatever is being considered a cyclic reference and allows the query to load.

So I have the original data as given.  I named this table "Raw" and I go through the steps to group (sum) sales by month.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7ITE5Mz1fSUTIEYQMDpVidaCW/1HKFyPyibKiwEVTYKb+4JD8PTS3CBCMQNsUwwQibCSBBQ6haH0eYAIaRxiCMaaQxNiON0Y1EFghOTSwpyUlFFo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [City = _t, Month = _t, #"YTD Purchases ($)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Month", Int64.Type}, {"YTD Purchases ($)", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"Incremental Sales", each List.Sum([#"YTD Purchases ($)"]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Incremental Sales", "Sales"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales", Int64.Type}})
in
    #"Changed Type1"

Then start a new blank query and use the following code:

let
    #"Table from Raw" = Table.RenameColumns(Table.FromList(Raw[Month], Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "Month"}}),
    #"Incremental Sales" = Table.AddColumn(#"Table from Raw", "Incremental Sales", each try Raw[Sales]{[Month]-1}-Raw[Sales]{[Month]-2} otherwise Raw[Sales]{[Month]-1}, Int64.Type)
in
    #"Incremental Sales"

[C0unt]

Thank you so much for the response dkay84.

I'm not very familiar with the Advanced Editor so it will take me some time to tinker around with your solution. Was hoping a single measure would have been the solution but I will definitely give yours a try. 

The example I provided is a simplified version, where-as my real data contains almost a million rows and a hundred columns of data. That said, I think I can still apply the same concept of creating a new query for months. Again, thanks for the response!

C0unt

[v-caliao-msft]

Hi @C0unt,

You can create a table using the DAX below.
Table = SUMMARIZE(Sheet2,Sheet2[Month],"Total",CALCULATE(SUM(Sheet2[YTD Purchases ($)]),ALLEXCEPT(Sheet2,Sheet2[Month])))
And then create a column
Column = IF('Table'[Month]=MIN('Table'[Month]),"(400~0)","("&'Table'[Total]&"~"&LOOKUPVALUE('Table'[Total],'Table'[Month],'Table'[Month]-1)&")")

The report looks like below

Regards,

Charlie Liao

[C0unt]

Hi Charlie, thanks for the response.

I was able to create the table as you indicated. However, the column that you are indicating to create is not simply a text of those cells in my graph. I need that column to calculate the total of YTD less the total of YTD-1 (or prior month YTD). So when I said "400-0" i was referring to the calculation of Month 1 YTD less Month 0 YTD. Then for month 2, the calculation would be Month 2 YTD less Month 1 YTD. Is there a way to create a calculated column that does this? Thanks!

[v-caliao-msft]

Hi @C0unt,

The calculated column is string data type which cannot be used to calculate YTD. And for your description " total of YTD less the total of YTD-1", the result is the total for that month, doesn't it?

Month       Sales      YTD     YTD-(YTD-1)

Month1       300       300       300-0=300

Month2       400       700       700-300=400

Month3       250       950       950-700=250

Regards,

Charlie Liao

[Amit_Saxena]

Hi , 

Need help, if i have monthly YTD numbers against month, how can i get the montly data, here for example if i have Month, and YTD then how will i calculate Sales, pls suggest?

Month       Sales      YTD    

Month1       300       300    

Month2       400       700    

Regard

Month3       250       950    

[dkay84_PowerBI]

Perhaps you are looking for something like a running total, but instead of adding each increment, you are finding the difference. However, from what I know of running total DAX patterns, they are not that simple either.

Take a look at this post:

http://stackoverflow.com/questions/26973744/find-rows-relative-to-current-rows-value-in-excel-dax

[dkay84_PowerBI]

No problem.  If it does end up resolving your problem, please mark as solved.  Good luck!