Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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!

  • Anonymous's avatar
    Anonymous
    6 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

  • Anonymous's avatar
    Anonymous
    Not 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.