Rolling sums, but on a text field...

[Zarlot531]

So I have built the below matrix showing the month lag it takes install orders. I plan to put this in a %-to-total view. 

But what really makes sense is to have this in a rolling format. so that, for example, one can see that by 3 months, say, 30% of orders have been installed, not just the % that was installed in month 3. The main problem I see here though is that that the "months" (Lag2) column is actually a text column. Why? Well, "12+" isn't a number, neither is "reversed." Reversed should actually not be rolled up since they never happened. 

Is this possible at all? Now that I think about it, I might be able to re-write my case statement at the SQL level to make, say, "3" equal 3 or less. Hmm

[JarroVGIT · Accepted solution]

Hi @Zarlot531 ,

See my PBIX here:

https://1drv.ms/u/s!Ancq8HFZYL_aiIkk9Sw0fCTwRWXgkw?e=Xsixhc

What did I do:

1. Create a calculated column named LagNumerical (based on Lag3 column)

2. Removed error rows from the dataset (rownumbers 621, 1004 and 1768) in Query Editor

3. Created the following measure:

Measure = 
VAR selectedLag = SELECTEDVALUE(Sheet1[LagNumerical], 100)
VAR minDate = CALCULATE(MIN(Sheet1[SoldMonth]), REMOVEFILTERS(Sheet1[LagNumerical]))
VAR maxDate = CALCULATE(MAX(Sheet1[SoldMonth]), REMOVEFILTERS(Sheet1[LagNumerical]))
RETURN
CALCULATE(SUM(Sheet1[RMR]), FILTER(ALL(Sheet1), Sheet1[LagNumerical] <= selectedLag && Sheet1[SoldMonth] >= minDate && Sheet1[SoldMonth] <= maxDate))

That seemed to have done the trick to have rolling sums of RMR 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Zarlot531]

Thanks again. I tried this, but just for experimental purposes I did < 5 for "0" (which obviously doesn't make sense but I'm just trying to figure out what the DAX is doing. And so it sums up the entire first 4 but for everything, and not related to year, as seen below. 

[Zarlot531]

I've been playing with the formula some. In the above formula, I don't understand why when I change <=2 to 1 or 3 or 0 the numbers underneath the 0 in the chart don't change at all. 

[JarroVGIT]

Is it possible to share the PBIX? I feel we are trying to solve problems we (on my advise ;)) created ourselves and we are straying away from the main goal. Alternatively, you can create an cleaned data sample and share that. That way I can play around with the model and see more quickly what is wrong. If you want, you can PM me the link (onedrive or google drive for example).

Kind regards,
Djerro123

[Zarlot531]

No worries, I wouldn't have even made it this far without your help. Below is a link to the data I'm using. Only thing I've done in Power BI is the measures we've discussed and a sort/index table to sort the columns, since the columns have "12+" and "Reversed." 

https://www.dropbox.com/s/a5ppegph8bbgdik/DataShare.xlsx?dl=0