Forum Discussion
Stop Page filter from one visual
Hi,
I have a page filter (InvoiceDate is on or after 1/1/2020)
The problem is there's a visual to display the results in 2019 only (as I have records dated from 2017 - 2020)
the measure =
what is this filter : ,FILTER(Sales,Sales[YTD Sales]="YTD")
You have to use all as dates are filter at page level
CALCULATE(COUNT(Sales[Vin]),FILTER(all(Sales),Sales[InvoiceDate].[Year]=2019))
You should prefer to use time intelligence with date calendar
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))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/Hi, amaniramahi
As is suggested by amitchandak , I created data to reproduce your scenario.
ALL is a useful function that returns all the rows of a table or all the values of a column, depending on the parameter you use. In all of its variations, ALL ignores any existing filter to produce its result. You can use ALL as an argument of an iteration function, such as SUMX and FILTER, or as a filter argument in a CALCULATE function. You may create a measure as follows.
Result = CALCULATE( COUNT('Table'[Value]), FILTER( ALL('Table'), YEAR('Table'[Date]) = 2019 ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
what is this filter : ,FILTER(Sales,Sales[YTD Sales]="YTD")
You have to use all as dates are filter at page level
CALCULATE(COUNT(Sales[Vin]),FILTER(all(Sales),Sales[InvoiceDate].[Year]=2019))
You should prefer to use time intelligence with date calendar
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))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/ - v-alq-msft
Community Support
Hi, amaniramahi
As is suggested by amitchandak , I created data to reproduce your scenario.
ALL is a useful function that returns all the rows of a table or all the values of a column, depending on the parameter you use. In all of its variations, ALL ignores any existing filter to produce its result. You can use ALL as an argument of an iteration function, such as SUMX and FILTER, or as a filter argument in a CALCULATE function. You may create a measure as follows.
Result = CALCULATE( COUNT('Table'[Value]), FILTER( ALL('Table'), YEAR('Table'[Date]) = 2019 ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.