Skip to main content
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


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. 


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! 


Super User
Super User



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


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?

Source = ConsolidatedAccounts,
LookupPrevMonthAmount =
{"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"},
RemovedColumnsFromJoin =
{"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 =
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"})
#"Reordered Columns"

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.
Please show the expected outcome based on the sample data you provided.

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


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


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

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors