Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |