Using ALLEXCEPT with DATESBETWEEN - Don't understand scope of ALLEXCEPT

[apup]

Let's say I have a widget factory, and it produces different types of widgets. I would like a measure that returns the percentage of one type of widget produced against all types widgets produced, for the previous month. Evenually, I'd like a chart like this, which shows the percentage of the total (for that month, and a 12 month average) for each type (I've accidently put "6 month" instead of "12 month" in the pic, but you get the idea). 

Link to file: https://ufile.io/q8um7

I have this measure:

Widgets1Mth = DIVIDE(CALCULATE(COUNT(Widget[WidgetId]),DATESBETWEEN('Date'[Date],DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),EOMONTH(TODAY(),-1))),CALCULATE(COUNTX(ALLEXCEPT(Widget,Widget[CreatedDate]),Widget[WidgetId]),DATESBETWEEN('Date'[Date],DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),EOMONTH(TODAY(),-1))))

 But obviously I'm doing something wrong as it doesn't respect the DATESBETWEEN filter in the second CALCULATE function.

Can you please illuminate me on how to use these correctly?

I found a solution to my problem. I'm using these measures:

CountWidget = COUNT(Widget[WidgetId])
CountWidget as % = [CountWidget] / CALCULATE([CountWidget],ALL(Type[Type]))
CountWidget as % - 1 mth = CALCULATE([CountWidget as %],DATESBETWEEN('Date'[Date],EOMONTH(TODAY(),-2)+1,EOMONTH(TODAY(),-1)))
CountWidget as % - 12 mth = CALCULATE([CountWidget as %],DATESBETWEEN('Date'[Date],EOMONTH(TODAY(),-12)+1,EOMONTH(TODAY(),-1))

I can't post a pic as we're playing with the backend currently, but I'll post one shortly.

[Ashish_Mathur]

Hi,

There is no mention of any date field in your dataset.  From just the image that you have shared, the DATESBETWEEN() function is obviously not required.  Can you share your dataset, the business question and the expected result.

[apup]

I'm performing this calculation for a single month.

So the result I'm expecting is the table above. There were many widgets produced in other months, but I don't wish to include them in this calculation, hence I use the DATESBETWEEN filter.

[Ashish_Mathur]

Hi,

Try this

1. From the calendar table, drag the month field to the Visual filter section and select any month.  Ensure that there is a relationship from the date column in your base data to the date column in your calendar table

2. Try this calculated field formula

=SUM(Widget[Qty])/CALCULATE(SUM(Widget[Qty]),ALL(Widget[Widget Types]))

[apup]

Thanks Ashish, but the results are still incorrect.

I have changed your formula slightly to:

TESTWidgets% = COUNT(Widget[WidgetId])/CALCULATE(COUNT(Widget[WidgetId]),ALL(Widget[WidgetId]))

This results in all types returning 100%.

If I change the ALL clause to:

ALL(Widget))

Then the percentage is measured against all widgets, instead of those in the filtered month.

I have made an example in Excel (I made a typo, the "6 mnth" should be "12 mnth")

[apup]

Updated first post, hopefully more clearly.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[apup]

Updated.

[Ashish_Mathur · Accepted solution]

Hi @apup,

You may refer to my solution here.

Hope this helps.