Forum Discussion

JihyeHeo's avatar
JihyeHeo
Frequent Visitor
6 years ago

Problem With Applying Page Filter (Date relationship issue)

I try applying a filter in the filter pane to the report to see the values for 2019. However, there is a difference between the filtered value and the actual one.    There are three tables as below...
Need Help
JihyeHeo's avatar
JihyeHeo
Frequent Visitor
6 years ago

Thank you for you reply. I use the below measure to count the number of new customers for YTD period, and to see the new customer# for 2019 YTD, I selected 2019 year in the filter pane.

 

Measure: New Customer Count YTD = calculate(DISTINCTCOUNT('New Customer'[FullCustNo]),DATESYTD('New Customer'[First Order Date]),'New Customer'[MNC Month]<=MONTH(TODAY()))

 

Please help me solving this problem.

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi JihyeHeo ,

I have created table B like this:

Try like this measure:

New Customer Count YTD =
CALCULATE (
    DISTINCTCOUNT ( 'Table B'[FullCustNo] ),
    FILTER (
        ALLSELECTED ( 'Table B' ),
        'Table B'[MNC Year]
            <= YEAR ( TODAY () ) - 1
            && 'Table B'[MNC Month] <= MONTH ( TODAY () )
    )
)

 

Sample file is attached that hopes to help you, please check and try it: Problem With Applying Page Filter (Date relationship issue).pbix 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • JihyeHeo's avatar
    JihyeHeo
    Frequent Visitor
    6 years ago

    Thank you for your reply. However, the problem is not solved...

     

    I compared the filtered 2019 value of a total new customer count YTD with the calculated 2019 YTD value.

     

    Please refer to the below measures.

     

    1. The filtered 2019 value of a total new customer count YTD by selecting 2019 on the filter field.

    New Customer Count YTD = CALCULATE ( DISTINCTCOUNT ( 'Table B'[FullCustNo] ), FILTER ( ALLSELECTED ( 'Table B' ), 'Table B'[MNC Year] <= YEAR ( TODAY () ) && 'Table B'[MNC Month] <= MONTH ( TODAY () ) ) )

     

    2. Calculated 2019 YTD value:

    Last year New Customer Count YTD = calculate(DISTINCTCOUNT('Table B'[FullCustNo]),'Table B'[MNC Year]=YEAR(TODAY())-1,'Table B'[MNC Month]<MONTH(TODAY()))

     

    Do you know why both values is not same?

     

    • v-yingjl's avatar
      v-yingjl
      Community Support
      6 years ago

      Hi JihyeHeo ,

      It is related with allselected() function. 

      The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

      If I do not use allselected(), when I set MNC year as 2019 in filter on all pages, it will get 4 not 3 in my sample:

      Please refer this document: allselected() function 

       

      Best Regards,
      Yingjie Li

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.