Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
50 | |
25 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |