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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

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. 

Tobiasahansen96_0-1671798405367.png

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! 

 

11 REPLIES 11
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1672033733622.png

 

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

Tobiasahansen96_0-1672041024504.png

FinanceAmount corresponds to the MoM change.

 

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

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

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
Super User
Super User

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

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)

DateBusiness UnitAmount YTDAmount Previous MonthAmount MoM
01/06/2022AMP5000500
01/07/2022AMP5005000
01/08/2022AMP5005000

 

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: 

 

DateBusiness UnitAmount YTDAmount Previous MonthAmount MoM
01/01/2022AMP000
01/02/2022AMP000
01/03/2022AMP000
01/04/2022AMP000
01/05/2022AMP000
01/06/2022AMP5000+500
01/07/2022AMP5005000
01/08/2022AMP5005000
01/09/2022AMP0500-500
01/10/2022AMP000
01/11/2022AMP000
01/12/2022AMP000

 

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

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

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

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..

 

 

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

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..

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors