YTD Numbers versus MoM change - Add a sales line for each item

[Tobiasahansen96]

Hi all, 

I have a problem with some data that i try to analyze. My data is imported via API connection by OData feed. 

The data are first of all specified in YTD values, and hereafter i have transformed it to calculate monthly values based on the YTD figures. 

But, between the YTD figures and the monthly values i have a difference when i add all the monthly changes together. 

On the above screenshot my total YTD should be 4.325.915, but when i add up the monthly values i get a lower figure. I have come to the conclucions that it is because of the 528.500 first being posted on June 1st, and then it stays the same YTD until August 1st, and hereafter it disappears because it is turned and therefore the YTD value is 0, but the 0 is not present in the data and therefore the monthly calculations does not get this change. 

Any possible solutions to such problem? Possible to create a custom line for each 1st day in each month, and if that has no value it just returns a 0, so i get a line for each month? 

Help would be appreciated! 

[wdx223_Daniel]

[Tobiasahansen96]

This is how my query looks with the name "ConsolidatedAccounts"

FinanceAmount corresponds to the MoM change.

[wdx223_Daniel]

from your code and snapshot, i can not get the logic of how to get the previous month amount

[Tobiasahansen96]

I can see why you can't get the logic. I have not sorted the table in the screenshot i have provided properly.. 

For the previous month, this is just the previous months YTD to number. I think it is easier to see in the screenshot in the original post. YTD = First column with amount, PreviousMonth = Second column with amount and MoM change = last column with amount. 

Hope it clarifies something otherwise please let me know

[Tobiasahansen96]

Hi, would this be something that i add to my current query or to a new query? Right now i have the following code, so my question is where do i add it?

let
Source = ConsolidatedAccounts,
LookupPrevMonthAmount =
Table.Join(
Source,
{"CompanyShortName", "CompanyLegalName", "CompanyRole", "Source", "AccountCode", "AccountName"
, "DimensionCode", "Dimension", "CurrencyCode", "AccountType", "ParentAccountCode", "Level", "GroupShortName", "GroupLegalName"
, "GroupStructure", "SortOrder", "PostingDatePrevMonth"},
Table.PrefixColumns(ConsolidatedAccounts, "PrevMonth"),
{"PrevMonth.CompanyShortName", "PrevMonth.CompanyLegalName", "PrevMonth.CompanyRole", "PrevMonth.Source", "PrevMonth.AccountCode", "PrevMonth.AccountName"
, "PrevMonth.DimensionCode", "PrevMonth.Dimension", "PrevMonth.CurrencyCode", "PrevMonth.AccountType", "PrevMonth.ParentAccountCode", "PrevMonth.Level", "PrevMonth.GroupShortName", "PrevMonth.GroupLegalName"
, "PrevMonth.GroupStructure", "PrevMonth.SortOrder", "PrevMonth.PostingDate"},
JoinKind.LeftOuter
),
RemovedColumnsFromJoin =
Table.RemoveColumns(
LookupPrevMonthAmount,
{"PrevMonth.CompanyShortName", "PrevMonth.CompanyLegalName", "PrevMonth.CompanyRole", "PrevMonth.Source", "PrevMonth.Year", "PrevMonth.Month"
, "PrevMonth.AccountCode", "PrevMonth.AccountName", "PrevMonth.DimensionCode", "PrevMonth.Dimension", "PrevMonth.CurrencyCode", "PrevMonth.AccountType"
, "PrevMonth.ParentAccountCode", "PrevMonth.Level", "PrevMonth.GroupShortName", "PrevMonth.GroupLegalName", "PrevMonth.GroupStructure"
, "PrevMonth.SortOrder", "PrevMonth.PostingDate", "PrevMonth.PostingDatePrevMonth"}
),
CalculateFinanceAmount =
Table.AddColumn(
RemovedColumnsFromJoin,
"FinanceAmount",
each if [AccountType] = "P/L" and [Month] = 1
then [Amount]
else if [Amount] = null
then - [PrevMonth.Amount]
else if [PrevMonth.Amount] = null
then [Amount]
else [Amount] - [PrevMonth.Amount]
),
ChangedDataTypeFinanceAmount = Table.TransformColumnTypes(CalculateFinanceAmount,{{"FinanceAmount", type number}}),
#"Reordered Columns" = Table.ReorderColumns(ChangedDataTypeFinanceAmount,{"CompanyShortName", "CompanyLegalName", "CompanyRole", "Source", "Year", "Month", "AccountCode", "AccountName", "DimensionCode", "Dimension", "CurrencyCode", "AccountType", "ParentAccountCode", "Level", "GroupShortName", "GroupLegalName", "GroupStructure", "SortOrder", "PostingDate", "PostingDatePrevMonth", "Amount", "PrevMonth.Amount", "FinanceAmount"})
in
#"Reordered Columns"

[lbendlin]

That is quite the opposite of the usual questions around YTD - interesting.  

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Tobiasahansen96]

Hi Ibendlin, 

Let me try to elaborate more on the issue. So i have the following data described here below (Data on revenue in a company)

Date	Business Unit	Amount YTD	Amount Previous Month	Amount MoM
01/06/2022	AMP	500	0	500
01/07/2022	AMP	500	500	0
01/08/2022	AMP	500	500	0

So as you can see for the above - I have a registered revenue in June of 500. This revenue keeps being 500 Year-To-Date until August, where it disappears because revenue goes to 0. So the monthly change when revenue is first recognised in june is 500, but when the revenue is 0 again the MoM change does catch this. My API source where i get the data from does not include rows where no data exists, so when i have a 0 i have no rows, which is why when revenue goes to zero, my MoM change does not recognise this because i have no lines that says 0, and therefore my PreviousMonth calculation does not catch this. 

What would be optimal is the following scenarios where for every date, where i dont have a line with revenue, there should be a zero with a corresponding date. So something like this: 

Date	Business Unit	Amount YTD	Amount Previous Month	Amount MoM
01/01/2022	AMP	0	0	0
01/02/2022	AMP	0	0	0
01/03/2022	AMP	0	0	0
01/04/2022	AMP	0	0	0
01/05/2022	AMP	0	0	0
01/06/2022	AMP	500	0	+500
01/07/2022	AMP	500	500	0
01/08/2022	AMP	500	500	0
01/09/2022	AMP	0	500	-500
01/10/2022	AMP	0	0	0
01/11/2022	AMP	0	0	0
01/12/2022	AMP	0	0	0

Hope it clarifies something otherwise please let me know! I really want to solve this issue. 

[lbendlin]

until August, where it disappears because revenue goes to 0.

I don't see that in your sample data. Did you mean September?

[Tobiasahansen96]

Yes i meant september, sorry! The main problem is in general when something is zero in total ytd figures i dont have a row for that particular month, which is why my MoM changes fails..

[lbendlin]

To report on things that are not there you need disconnected tables and crossjoins 

[Tobiasahansen96]

Thanks, i will try to look more into that but i have tried to merge queries with dates but because of my data it doesnt seem to work properly..