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, 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.

 

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • 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.