Display previous data when using a date filter

[Anonymous]

Hi all, I have a basic table that shows the Date, Status, Project Sales. In this case the status is important beacuse I want to show only the ones that are active. So I just use a filter for that. My problem is that I have a slicer for the date, so I want to show an specific date using the slicer but that the values in the table shows not only the filter date but also to display the previous dates. This helps beacause I want to show the entire active products. In other words, I have YTD data and if as an example I filter with an slicer 04/10/2020 I want my table to reflect not only 04/10/2020 but also previous values. 

Thanks a lot.

[v-janeyg-msft · Accepted solution]

Hi， @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can use filter pane to filter the desired dates.

Like this:

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[amitchandak]

@Anonymous , Previous is previous Day, Week, Month, Qtr, Year?

With help from the date table, you can get all these. Refer my blogs for that

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

[Anonymous]

@amitchandak Imagine that I have data YTD. I use a date slicer and filter 01/10/2020 so I want my table to reflect from 01/10/2020 and all previous values.

[amitchandak]

@Anonymous , Try like examples

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"))
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))

YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('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)

[Anonymous]

@amitchandak I think I don´t quite explain myself well: Imagine that I have this table (Table1). So if I use a slicer and filter 25/10/2020 I want my table to be as Table2.

Table1

Date	Status	Product	Volume
29/10/2020	Active	Laptops	10,000,000
29/10/2020	Active	Laptops	50,000
29/10/2020	Active	Cellphones	80,000
28/10/2020	Active	Cellphones	50,000
25/10/2020	Active	Cellphones	50,000
15/10/2020	Active	Cellphones	10,000,000
02/10/2020	Active	Desktop	10,000,000
29/09/2020	Inactive	Desktop	80,000
25/09/2020	Inactive	laptop	80,000

Table2

Date	Status	Product	Volume
25/10/2020	Active	Cellphones	50,000
15/10/2020	Active	Cellphones	10,000,000
02/10/2020	Active	Desktop	10,000,000
29/09/2020	Inactive	Desktop	80,000
25/09/2020	Inactive	laptop	80,000

[Ashish_Mathur]

Hi,

If Table2 is the result you want to generate from Table1, then Blog article here may help - http://www.ashishmathur.com/flex-a-pivot-table-to-show-data-for-x-months-ended-a-certain-user-define....