selected filter date show day before and last

[ninakarsa]

Hi All,

I would appreciate your help with the following calculations: I want to create a report with a date filter, and based on the date selected the following table A will be populated. Data in Table B

For example, i choose date 26/01/2019

the sales today =26/01/2019, sales yesterday=25/01/2019, sales same day last week=19/01/2019 and sales same day last year=27/01/2018

filter date

26/01/2019

Table A:

Product	Sales Today	Sales yesterday	same day last week	same day last year
Product A	660	570	390	210
Product B	1290	1200	1020	840

Table B

Product	Date	Sales
Product A	26/01/2018	120
Product A	27/01/2018	210
Product A	18/01/2019	300
Product A	19/01/2019	390
Product A	24/01/2019	480
Product A	25/01/2019	570
Product A	26/01/2019	660
Product B	26/01/2018	750
Product B	27/01/2018	840
Product B	18/01/2019	930
Product B	19/01/2019	1020
Product B	24/01/2019	1110
Product B	25/01/2019	1200
Product B	26/01/2019	1290

[v-easonf-msft]

Hi , @ninakarsa 

Here we go.

1.Create a calculate table as   data slicer

Slicer = DISTINCT('Table B'[Date])

2.Then  try to use measure as below 

Sales Today = CALCULATE(SUM('Table B'[Sales]),FILTER('Table B','Table B'[Date] in FILTERS('Slicer'[Date])))

Sales yesterday = CALCULATE(SUM('Table B'[Sales]),FILTER('Table B','Table B'[Date] in DATEADD(FILTERS('Slicer'[Date]),-1,DAY)))

same day last week = CALCULATE(SUM('Table B'[Sales]),FILTER('Table B','Table B'[Date] in DATEADD(FILTERS(Slicer[Date]),-7,DAY )))

If you are sure that the  sales same day last year=27/01/2018 

same day last year = CALCULATE(SUM('Table B'[Sales]),FILTER('Table B','Table B'[Date] in DATEADD(FILTERS('Slicer'[Date]),-364,DAY)))

If   the  sales same day last year= 26/01/2018,then 

same day last year = CALCULATE(SUM('Table B'[Sales]),FILTER('Table B','Table B'[Date] in DATEADD(FILTERS('Slicer'[Date]),-1,Year)))

Here is a demo :

pbix 

Best Regards,
Community Support Team _ Eason

[Greg_Deckler]

OK, you should be able to create 4 measures for that. 

Sales Today = SUMX(FILTER('Table B',[Date] = TODAY(),[Sales])

Sales Yesterday = SUMX(FILTER('Table B',[Date] = (TODAY() - 1) * 1. ,[Sales])

Can you time intelligence for the other two or, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

Try with a date calendar

 today =CALCULATE(SUM(Sales[Sales Amount]))
 today =CALCULATE(SUM(Sales[Sales Amount]),'Date'[Date]=Today())
 today =CALCULATE(SUM(Sales[Sales Amount]),'Date'[Date])
 last day Sales = CALCULATE(SUM(Sales[Sales Amount]),(dateadd('Date'[Date],-1,DAY)))
 yesterday=CALCULATE(SUM(Sales[Sales Amount]),'Date'[Date]=Today()-1)
 same day last week Sales = CALCULATE(SUM(Sales[Sales Amount]),(dateadd('Date'[Date],-7,DAY)))
 same day last year 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/