Previous Yeaar Total

Hello:

The title makes this seem simple. I’m having trouble coming up with with a sum for prior year.

To arrive at the current year I had to patch a couple of fact tables together as the figures begin in the year 2000. The correct answer is a cumulative total of all the amounts since 2000.

I have one grouped by fact table from 2000-2017 and then a detailed level table for 2018 to current which is used in a number of other areas.

I have attached the steps I have taken to arrive at the correct figure for 2020 but getting to same period last year is where I do appreciate some help.

I’m not permitted to share the file do to a confidentiality agreement.

Thank you very much! Happy July 4th to those who celebrate

Accounts Receivable Question.pptx (58.4 KB)

Best regards,

Bill S

@Whitewater100,

I appreciate your effort in putting the PowerPoint together to explain your problem. However, I think this one is too complex (for me, at least…) to solve without a PBIX to look at the data, data model, existing measures, etc.

Below is a video I put together providing some quick techniques in Power Query to mask sensitive data so that it can be posted on the forum for this purpose. If you’re able to provide that file, I’m confident that collectively we can diagnose the problem and provide a good solution.

Thanks.

  • Brian

Hi Brian:

OK and thank you for replying. I will check out the video and see how I can put it together for a solution.

i hope to get to it but it’s a little hectic today!

Thanks again for replying.

Bill

Hi Brian and Community:

My revised file is exceeds the max upload size. Any suggestions are welcomed as it was a bit of a task to reconstruct for the forum.

Thank you!!

Bill S.

@Whitewater100,

Thanks – two options here:

  1. remove X% of the records in the file to get it down in size (go to the 9:30 mark of the video above for how to do this)

  2. post the file on One Drive or Google Drive or Dropbox and post the link on the forum

Option one is a bit more preferable, since it will make it easier to send you back a solution file that can reside on the forum, but Option two is okay if necessary.

  • Brian

Hi Brian:

I consolidated one table and will try to upload now. Ont table is 871 rows and the history table (2000-2017) is down to three columns. I will try the upload again and then go to option number two if needed. My file is 10KB.

Thank you for your help!

Best regards,

Bill

Hello Brian-Community:

I have posted a link to Google Drive to allow a more in depth look at my previous year question.

Please see below. Thank you. Bill

https://drive.google.com/file/d/1-7FHwqgpaZffn9OVm7ZlRoQPB4nvZKmL/view?usp=sharing

@Whitewater100,

One quick question - when you refer to “this year”, do you mean the current year ( YEAR( TODAY() ), or the year corresponding to the max year selected on your date slicer? In the example you posted they are both 2020, but obviously wouldn’t necessarily be the same year with a different slicer selection.

Thanks for clarifying.

  • Brian

@Whitewater100,

I can see why this one’s been giving you trouble - I’ve been working on it for a while and haven’t gotten it to work right yet either. You’ve done a really nice job constructing your measures via measure branching. I’ve been going through trying to dynamically step each of the nested measures back a year dynamically, but I’m trying to figure out at which of the steps am I’m currently going awry.

When you calculated A/R Balance LY, did you also calculate as part of that exercise the correct LY values for TotGL2017, Posted GL Amount, Current AR Balance, and AR2017? If you could please post those in response, that would help me debug my current solution.

Thanks very much.

  • Brian

Hi Brian:

I’m trying to compare 2020 ( most recent date where transactions exist)

Last Day with Sales = CALCULATE(MAX(Sales[Posting Date]), ALL(Sales)

with the same date from last year(now 2019).

To get the latest 2020 answer I add up all of the 2000-2017 file plus all of the 2018-current file.

To get current year you’ll see my slicer is set from Jan 1 2018 to a most recent date in 2020.

Ideally I would like to be able to put the slicer to any date in 2020 and be able to compare to the same time frame cumulatively for last year.

I inserted an image of the results that should tie out thru 6-14 TY v LY on page one of the report.

If we can only do most recent figure for 2020 vs same date last year, that would be acceptable.

We just got back from vacation last night, so sorry for the delay.

Thank you again for helping me.

Best regards,

Bill

)

Hi Brian:

I tried something that seems to potentially work but is weird. Your suggestion about the sum of the static history led me to try doing previous year (DATEADD) on only the 2018 to current table and then add it back to the static “thru2017” total.

On the attached file I have to the slicer to 10-01-2017 to arrive at the desired totals. (I don’t understand or like this part).

Since this measure is one of many tricky ones in my report I would like a solution where it is not so jerry-rigged. (sp?).

What do you/forum think?
Many thanks again!!

https://drive.google.com/file/d/1kjFGCtC8SPhOrszdxY2aW_WAz9lW1O40/view?usp=sharing

@Whitewater100,

Thanks very much for the additional information and clarification – that’s a big help. I’ve got a chunk of time blocked off after work to dig in on this, and hope to have a solution back to you sometime tonight. Seems we’re taking similar approaches in trying to crack this one, so will be interesting to see how it ultimately shakes out.

Interesting problem though. I’m having a good time trying to unwind it all - will keep you posted on my progress.

Hope you had a good vacation.

  • Brian

Hi Brian:

I’m excited for the assist and your time is very much appreciated. Overall Other items are figuring out nicely but I have a couple of other questions but figure this one is big enough for now.

Have a nice night!

Best regards,

Bill

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!

@EnterpriseDNA,

Can we please keep this one open a while longer? I got bogged down on another difficult solution, and have not had the opportunity to provide @whitewater100 with an adequate solution here. I’m still working it, and tried unchecking the solution earlier but it got closed out again.

Thanks very much.

– Brian

P.S. For the same reason, I would like to keep @ankit’s recursive post open as well through the weekend.

1 Like

This post has been reopened @BrianJ. Thank you for all your effort in continuously working in finding solutions and dedication to the Enterprise DNA community.

1 Like

Hi Brian:

Just checking in. Thank you,

Bill S

@Whitewater100,

Started back in on this last night and have a block of time reserved today to work on it. Sorry for the delay and thanks for your patience.

  • Brian

@Whitewater100,

OK, I think I know what the problems are:

  1. The main issue was that your Ledger_to_17 table was way out of range of your Date table. The former starts in 2000, while the latter starts in 2017. This is why you only got correct figures when you set the slicer to 2017 or later, since at that point all the fact tables were within range. I altered your date table query to start at 1/1/2000.
  2. One of your fact table dates (I think it was posting date) was set to date/time type. I reset this to date type.

Revised file posted here:
https://drive.google.com/file/d/1RQl1YXzN2RKquTJr-9tCZZWoq1PtYVTj/view?usp=sharing

I believe this should solve your problems, but let me know if it doesn’t.

Thanks.

  • Brian

Hi Brian:

I wasn’t aware you had replied to me, so sorry for my delayed response. What you say makes a lot of sense and is helpful.

I ended up using DAX Studio to find out only six figures out of 51 accounts had any history to be tallied from 2000-2017. I made a small table without specific dates and added that to the results from my 2018 to current table. Now I don’t have to wok about that huge file and it remains static.

Again, thank you for looking into a great solution for me!

Best regards,

Bill