Hi Brian
Thanks for taking another look. Hope you got some sleep.
The total for the month year visual should ideally be the average of the month DSO’s for each month end date in context of a from/to date slicer.
I can see, having said that, the difficulty in working the DAX thru so that it would run quickly and I think this is a nice to have. More importantly is the issue with getting the average at the same time point across a series of accounts - ie the other part of the problem.
When I responded I though they were the same thing but obviously not.
I’m moving another aspect of this report on to identify overdue amounts at a timepoint/dynamically.
That’s proving a bit of a challenge too! But I’ll keep on it and log another issue if I can’t overcome. It’s interesting how what seems the simplest thing can be difficult and what seems difficult can sometimes be very easy!
For background I work as Finance Director in the Motor trade and have come through a year’s learning curve on Power bi, written reports for Management Accounts, Service job tracking, vehicle sales profitability, service, bodyshop and parts sales tracking. I’d have given up a long time ago without this forum. SO thanks again for your help.
I found some time over the weekend to review this and whilst the DAX is working as designed. i think the commercial logic which I was applying was flawed.
So I have reverted to a more traditional approach to calculate DSO based on month summary totals.
I have created a table at time of data refresh with columns for account, balance and sales amounts for the each of the previous 6 months. From this history table I can create a measure to do the job easily up to previous month end, which is fine.
Having put a different solution in place I feel I’ve been wasting your time, but I do always learn a bit more when you respond to my posts. So apologies for a wasted journey, but thank you for imparting some more knowledge on DAX.
No apologies needed. I always find your questions interesting and this one was no exception. I learned some things through the process of working through this as well. Just glad you were ultimately able to get to a good solution, even if it took us a while to get there…
Also, were you able to solve the overdue invoice calculation issue you mentioned? I checked the forum and searching on “overdue” produces a lot of solutions related to that question. As always, just give a shout if needed.
