DIsplay data from previous date depends on slicer

[Anonymous]

Dear friends

how can i let data always display as date-1 depends on selected date on slicer for example

Date	Product
01-01-21	A
01-01-21	A
01-01-21	A
02-01-21	B
02-01-21	B
03-01-21	A

how can i count product "B" on date 2 -1-21 but slicer selected on 3-1-21

thanks you in advance for any suggestion

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Try follow steps:

1.create calaulated  table "calendar"

Calendar = CALENDARAUTO()

2.create measure as below:

CountB = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Product] = "B"
            && 'Table'[Date]
                = CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( 'Table', 'Table'[Date] < SELECTEDVALUE ( 'Calendar'[Date] ) )
                )
    )
)

Please check my sample pnbix file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-easonf-msft I have tried this but measure show as blank , are u have any suggestion : (

[v-easonf-msft]

Hi, @Anonymous 

Have you check the relationship between tables?

In my demo, there is no relationship between the Calendar table and the Data table.

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft i will try to switch off relationship

[Anonymous]

@v-easonf-msft Thank you so much!

[amitchandak]

@Anonymous , with help from date table and time intelligence

example

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9