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!

 

[GDRIII]

Not sure if this will help but, could you use CALCULATE with DATESBETWEEN and create a FIRSTDATE LASTDATE for your betweeners?

 

[scottsen]

Yikes. This one is really tricky. I like it!

So, the short answer is "no", there is no cute way to "break out" of a measure. We HAVE to find a way to filter this data, and like GRRIII is suggesting, it is likely by dates.

It's like we are trying to write something like: =CALCULATE([WhateverMeasure], FILTER(ALL(Calendar), SUM(Sales[TheSales]) >= AR[TheAr]))

But that would give us no "ordering" (and probably isn't even valid dax )

My gut is to use ADDCOLUMNS to dynamically create the running total, then use that... somehow? But this is tricky enough that it will take me some time, and I just have too much client work this week

Somebody flesh this out for me

 

[Salle]

Hello!

Thanks for the replies!

Yes, i could use the DATESBETWEEN function, however i would need to make the FIRSTDATE function dynamic.
If there is a way to make a counter for how many months to iterate i could place it in a PARALLELPERIOD, however finding the way to make the counter is the tricky part.

I havent tried ADDCOLUMNS, but I will definetly try it! Like you say scottsen, it should be some sort of running total.


The experimenting continues...

P.S. is there a way to view the table data types? It would be much easier to see the tables that are generated in functions such as SUMMARIZE etc.
I have tried DAX Studio but i cant get it to work.

 

[scottsen]

DAX Studio is probably the easiest way. But if you are using 2013, you can also use the technique I blogged about here: TopN as viewed by DAX Table Queries « PowerPivotPro

I suspect that "in the end" you will not want a (static) calculated column, but you might start there for your running total. Then convert that to an ADDCOLUMNS style measure once you get that going?

 

[scottsen]

Salle, see if this gives you any ideas...

https://www.dropbox.com/s/w2aqg5d3ub1x9hz/DSO.xlsx

(It's a 2013 file, if you need 2010 I can recreate it for you).

 

[Salle]

Exactly like that, mapping each row to a monthid determined by Sales and AR!

So, to create it dynamically, the table generated by the measurement should look like it does in the PP-window in your excelfile.

I will use your file and build on the idea that you've started! I will try the ADDCOLUMNS measure. Very helpful, thank you so much for taking your time with this!

 

[Salle]

Hi!
I finally solved it!

Here is the DAX formula:

=SUMX(
ADDCOLUMNS(
FILTER(
GENERATE(
SUMMARIZE(ALL(D_Tid);D_Tid[DATE];"sales";[SALES]);
SUMMARIZE(D_Tid;D_Tid[DATE2]));
ISBLANK(D_Tid[DATE]) = FALSE() &&
ISBLANK(D_Tid[DATE2]) = FALSE() &&
(EDATE(D_Tid[DATE];5) >= EDATE(D_Tid[DATE2];0) &&
EDATE(D_Tid[DATE];0) <= EDATE(D_Tid[DATE2];0)));
"rest";CALCULATE([REST_AMOUNT];DATESBETWEEN(D_Tid[DATUM];D_Tid[DATE2];D_Tid[DATE2]));
"cumusales";CALCULATE([sales];DATESBETWEEN(D_Tid[DATUM];D_Tid[DATE];D_Tid[DATE2])));
IF([cumusales] > [rest];0;1))

It creates two tables which produces another table through the GENERATE function (a cartesian product). The filter function removes all blank and unwanted rows so it leaves a table with three columns:

-All months to be included
-Sales for these months
-The base month on which to calculate DSO

Then i put in two additional columns:

-Rest amount for base month
-Cumulative sales

Finally, SUMX adds a "1" for each time the cumulative sales does not exceed the rest amount and "0" for each time it does. This can be regarded as some sort of substitute to "Exit for" in VBA in a for loop

Basically, the function creates a counter for how far back to add the sales amount.

I restricted the function to only go 6 months back.

Nice to have finally solved it, been struggling with this for a while!

/Daniel

 

[scottsen]

Like a total boss!

 

[Salle]

Thanks!