Latest Enterprise DNA Initiatives

Need to grab a previous value

DAX Calculations
#1

I have a report that I publish to the PowerBi service that has been working well until we crossed over into 2021.

What I have is data coming from Microsoft Dynamics Account Schedules.

What happens I assumed Sales YTD numbers that come in would reset back to zero and there would be nothing for me to have to handle. I was wrong and what is happening is the YTD totals from 2020 are being carried over until our accounting department closes the year. The issue that I am dealing with is the data source that I am pulling from will not have certain numbers updated until almost the end of the first quarter of the year.

My approach is to just look at December of 2020, and if I am viewing data in any month in 2021, subtract the last numbers in December from the 2021 total and that would leave me with the 2021 YTD versus an aggregate number. My second concerns is at some point Accounting will close 2020 and then the 2021 numbers will then line up.

I can lookup the specific the record I want from December 2020 based in Date, KPI_Code, and department from December 2020.

Looking for suggestions on handling this issue. I have attached my sample PowerBi file. If you look at the file and change the slicer dates from December 2020 to January 2021 you will see that the YTD values stay the same due to the data coming in from Microsoft Nav.

DailyDoc2021.pbix (434.3 KB)

Also, can anyone tell me why I keep getting blank on the measure below.

calculate capture
calculate capture1254×557 56.4 KB

#2

Hi @lomorris, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

#3

@lomorris,

With regard to how to handle the year-end balance issue, I will defer to folks like @Harsh , @MudassirAli and @GarryA, who have expertise in these types of financial accounting issues.

But in terms of your measure returning blank, just try wrapping your table reference in ALL() to remove the prior filters that are preventing the current measure from reaching the record that meets the filter conditions in this measure. Per below, I believe that revised measure now returns the correct result.

Test = 

CALCULATE(
    SUM('Account Schedule'[Balance_at_Date_Actual] ),
    FILTER(
        ALL( 'Account Schedule' ),
        'Account Schedule'[KPI_Code] = "10" &&
        'Account Schedule'[Number] = "2556"
    )
)

image

I hope this is helpful.

– Brian

#4

Thanks Brian,
I was completely overlooking the current context and clearing existing filters.

Thanks.

Yup that whole year-end balance thing added a bit of complexity that I have to now account for.

#5

Brian,
Another quick one. For the code below
CALCULATE(
SUM(‘Account Schedule’[Balance_at_Date_Actual] ),
FILTER(
ALL( ‘Account Schedule’ ),
‘Account Schedule’[KPI_Code] = “10” &&
‘Account Schedule’[Number] = “2556”
)
)

If I wanted to filter based on rows with the Account Schedule date = “12/01/2020” versus Account Schedule number = 2556.

Your thoughts?

#6

@lomorris,

Sure - just tweak the measure in the following way:

image

  • Brian
#7

Thanks Brian, that works.

#8

@BrianJ @lomorris Generally if you want financial FACT data across a time period its best not to use the account balances calculated/reconciled by an application/people at certain points in time.

I noted a lot of people use this method to derive a report balance from a dimension like an account name or number but there is no way to easily reconcile this against the master facts a trial balance’s accounting system provides.

Its best to look at the source FACT ledgers to derive reconcilable insights at particular points in the reporting period. Accountants will always look to a trial balance to secure accurate figures for balance sheet, profit and customer reporting obligations.
The sales team will look at sales ledgers or its sub ledgers which have the detail… I have previously discussed trial balance construction here.

And customer sales reporting here…

Anyway, I hope that gives you some ideas about how to approach reporting on accounting system data…! Best of Luck with the project!

3 Likes
#9

@GarryA,

Great info – thanks for the detailed and timely response (you’re like the Accounting Genie, who magically appears when these sorts of difficult issues come up).

Nice to see you back here – hope you’re doing well.

Thanks again.

  • Brian
1 Like
#10

Garry,
Thanks too for this information. I totally agree with you on this. I am not an accounting person, but it’s pretty clear to me that using the various transaction ledgers we have takes me right to the real transaction data. It will be interesting to see if the raw transaction data matches what is coming from the Accounts schedules which as I am sure you already know are fed by the Chart of Accounts.

#11

Hi fellas, @BrianJ @lomorris

What I tend to do is look at the granularity of the data required by the report users and what ledger I need to query.

This generally, will lead you to the correct source ledger once you understand how the accounting systems data around the particular ledger (in your case Customers Sales) facts is built. …It can be a bit of a rabbit hole to be honest as each software provider has a slightly different way of doing this but the core logic of a trial balance holds true for any accounting system and it proves as a test I use to confirm accuracy of control and sub ledgers for Customer Sales and Supplier Purchases in any business.

Sales figures are generated by the Sales Ledger so you want to query this data source endpoint via looking at the database tables on your SQL server on premise or hitting up the API for cloud data sources which it looks like you have.

The sales ledger normally has a control and sub ledgers at a daily granularity that hold goods and service transaction ledger details, depending on your report requirements you can obtain the specifics or a summary from these tables.

This will then allow you to use the Sales facts transactions to do any time intelligence calculations you need and avoid the current approach of using account balances generated by a dimension like the chart of accounts.

Anyway good luck with the reports!

1 Like