Forum Discussion

yasbos's avatar
yasbos
Icon for Resolver II rankResolver II
3 years ago

Date Filter: unexpected results

Hi. I am trying to figure out this strange issue. I have an accounts table, date table, code-mapping table, Loc_Dim. I am using a clustered bar chart to plot the YTD accounts opened by line of business, which is coming from the Loc_Dim table. I calculate the YTD accounts opened as below:

 

Accounts Opened YTD =
var vCurrentDate=Calendar_Dim[Current_Date]
return
calculate(sum(acct_Fact[Accounts Opened]), filter(OpenDate_Fact,year(acct_Fact[Open Date])= year(vCurrentDate)))
 
The Calendar_Dim[Current_Date] measure is in the calendar table and is:
Current_Date = calculate(max(acct_Fact[Open Date]),removefilters(Calendar_Dim[Date]))
 
The relationships are as follows:
Loc_Dim is linked 1-to-many to the acct_Dim table. Calendar_Dim is linked 1-to-many to the acct_Dim table
 
The problem is that, when I have a date slicer from the calendar_Dim table, and I set the date range to exclude the current year (just testing), I'm expecting to see nothing in the clustered bar visual. However, I do see just two out of about 12 lines of business have values. That's strange because I excluded the current year in the visual, and the x-axis plots the Accounts Opened YTD measure (defined above.) The curiosity gets even more intense when I decide to remove the line of business (which is coming from the Loc_Dim) from the y-axis because now I get zero accounts as expected. It seems that, somehow, the filter context that the line of business creates in the visual breaks the logic.

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    yasbos , Hop open date is joined with Calendar_Dim , then you can have measure like

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s