Forum Discussion
MAX/MIN without context filter but with one page filter
Hi,
I have a table like this:
Reseller Name | Reseller Agent | Count of Days | Sales
I have a couple of page filters like reseller name, reseller agent, year-quarter and year-month.
Let's say I filter year-month = 2019-10
Now what I want is :
Reseller Name | Reseller Agent | Count of Days | Sales
ABC | abc | 30 | x
ABC | xyz | 30 | x
I want to create a count of days ignoring the context filter of reseller name and agent (by that ignoring the number of days they actually made a sale) but just depending on the date that I filtered for. I tried something like this:
CALCULATE(MAX(FCT_Sales[SALES_COMPLETE_DT_KEY]);ALLEXCEPT(FCT_Sales;FCT_Sales[SALES_COMPLETE_DT_KEY]))
- CALCULATE(MIN(FCT_Sales[SALES_COMPLETE_DT_KEY]);ALLEXCEPT(FCT_Sales;FCT_Sales[SALES_COMPLETE_DT_KEY]))
However, I get 730 as result which is the total number of days without the year-month filter.
The date key is a numerical value, e.g. 12345, which is connected to another table DATE_DIM where you can find year-quarter and year-month which I use as filter.
I tried a couple of hours different possibilities but I just can't get it work.
Can anybody help?
Thanks in advance!
- Anonymous6 years ago
Finally managed to do it:
CountOfDays = SUMX(DISTINCT(DT_DIM[YR_MTH_NO]);CALCULATE(AVERAGE(DT_DIM[MTH_NO_OF_DAYS])))
In the date dim, I had the column MTH_NO_OF_DAYS with 31 for January, 30 for February etc.
1 Reply
- AnonymousNot applicable
Finally managed to do it:
CountOfDays = SUMX(DISTINCT(DT_DIM[YR_MTH_NO]);CALCULATE(AVERAGE(DT_DIM[MTH_NO_OF_DAYS])))
In the date dim, I had the column MTH_NO_OF_DAYS with 31 for January, 30 for February etc.