Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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!
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
24 | |
19 | |
14 | |
10 | |
7 |