Problem With Applying Page Filter (Date relationship issue)

[JihyeHeo]

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, and I have created some relationships between Table A [date] and Date columns of Table B & C (Many to one, single direction) and made the relationship between Table A [date] and Table C [invoice date] active. 

• Table A: Calendar table including the columns of date, year and month
• Table B: New Customer table including the columns of customer name, the first order date/year/month
• Table C: Sales table including the columns of customer name, invoice#,invoice date, entry date, booked date and order values

When selecting a filter as Table A [year], the value for 2019 YTD New Customer# is 201, and when selecting a filter as Table B [year], the value is 205 and this is the correct one.

I need to apply Table A [year] to the "page filter" in this report. How can I get the correct value for 2019 YTD New Customer#?

I hope you will help me solving it.

[amitchandak]

@JihyeHeo , What is you mean my 2019 YTD. It till Jun 2019?

Examples Various version of YTD you can have

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

//Based on Today
YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

//Based on Transaction date. Here you can change logic
YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[JihyeHeo]

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]

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]

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]

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.