SUMX until

[Salle]

Hello,

Does anybody know if there is a way to create an iteration with an exit block in DAX?

I want to create a measurement for DSO (Days of Sales Outstanding).
It uses two amounts: accounts receivable (AR) in current month and sales, and it adds the sales amount for each month until it exceeds the amount of accounts receivable.

For example:
- AR is 1000 in April.
- Sales is 500 in April, 300 in March and 400 in February.

In this scenario the measurement should iterate until February.

I have been experimenting with SUMX and SUMMARIZE but it haven't worked out.

If someone has any tip or pointer to a solution i would be very grateful! Have been struggling with this for a long time!

Cheers!

 

[XLBob]

interesting post. Salle, are you happy to share your sample workbook if you have one?

 

[Salle]

Absolutely!
The workbook i'm working in is very messy at the moment.
I will post a cleaned up sample version asap!

 

[Salle]

Ok, here it is:
https://www.dropbox.com/s/1hb77v0jizlak68/Sample workbook DSO.xlsx

I have never shared a file from Dropbox so i hope it works!

The measurement i have been working on is the one called "DSO_COUNTER", the rest are just buildingblocks for obtaining the measurement i want to use - "DSO".

I made a small change from the measurement i described in the earlier post. Instead of the first SUMMARIZE function i used VALUES. Dont know if it improves the performance but it shortened the formula a bit

Surely it can be improved, so if you (or anyone else for that matter) has any tips, please let me know!

You may find the names of the measurements to have weird names. They have mainly swedish names

/Daniel