Relative Date Slicer

[Anonymous]

Hi, 

I have a report with a relative date slicer using date column. Slicing is always done from today onwards.

This slicer filters a matrix which reports on weekly basis using week ending date for aggregation.

The matrix looks like this

	WeekEnding
Material	23.08	29.08
A	Stock levels
B	Stock levels
C	Stock levels
D	Stock levels

Consumer use relative slicer to filter by week. Because relative slicer range spans number of weeks x 7 days from now, it covers a part of the following week. So that if it is 20th of August 2021 then relative slicing on 1 week will make the matrix display data from the 20th to the 26th. But 23rd to 26th is the next week and I do not want to display it in the matrix, because it is an incomplete weekly view. This logic is valid for more than one week time frame, too, so that if I slice the data on 2 weeks then I want to have week 1 (current) and week 2 (the next) data displayed only.

I do not keep historical data, so today is always the first day.

I was thinking of some kind of measure to hide away last week's value from the matrix, if the last week is not complete.

Thanks

[Anonymous]

Hi @Anonymous ,

Could you please provide the raw data information and the results you are looking for? Do you have a date dimension table in your data model and is the data displayed in weeks on matrix? You said that the table does not keep historical data, does this mean that the table only has data for the present and for future periods? According to your example, if it slice on Aug 20th, is the data only displayed on the matrix as shown below?

Best Regards

[Anonymous]

Hi @Anonymous ,

Thanks for your reply. 

This is the sample data

Then I slice on 1 week and get result as below (stocks are summed up for each week)

10 is not a correct value for the week ending on the 5/09. It only considers 30/08 not other values.

In this case I only want to show the week ending on the 29th.

Addressing you questions:

1. Do you have a date dimension table in your data model and is the data displayed in weeks on matrix?

Yes, I have date dimension table which is connected 1:many to the table containing stock values. Both Date and Week Ending fields come from that Date table.

Data in the matrix is summed up by week.

2. You said that the table does not keep historical data, does this mean that the table only has data for the present and for future periods?

Correct

3. According to your example, if it slice on Aug 20th, is the data only displayed on the matrix as shown below?

Correct

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) base on your provided data, please check whether that is what you want.

1. Create a measure as below to get the sum of stock base on the specific conditions

Measure = 
VAR _maxdate =
    MAX ( 'Date'[Date] )
VAR _selweekedning =
    SELECTEDVALUE ( 'Date'[Week Ending] )
RETURN
    IF (
        _maxdate < _selweekedning,
        BLANK (),
        CALCULATE (
            SUM ( 'Table'[Stock] ),
            FILTER ( 'Table', 'Table'[Date] <= _maxdate )
        )
    )

2. Create another measure to get the correct total values

Measure 2 = SUMX ( VALUES ( 'Date'[Week Ending] ), [Measure] )

Best Regards

[Anonymous]

Hi @Anonymous ,

Thanks for your solution.

It works for me.

I also came up with this one which seems simpler.

ClosingStock - Full Weeks Only =

VAR _lastDay = WEEKDAY(LASTDATE('Date'[Date]),2)

RETURN

CALCULATE([Stock], FILTER('Date', _lastDay = 7))

[Stock] is another measure I created for calculating weekly stock only.

I also have other visualizations on the same page. Those visualizations do not have WeekEnding context. Can I somehow implement the same logic as a page-wide date filter? 

Thanks
Evan

[Anonymous]

Hi @Anonymous ,

I'm not very clear about your requirement. Could you please provide your simple sample pbix file or some screenshots to describe your expected result? Thank you.

Best Regards

[Anonymous]

Hi @Anonymous ,

Are you still looking into this?

Thanks

[Anonymous]

Hi @Anonymous ,

I will re-phrase the question to my particular situation.

I have a visual where there is no WeekEnding context but daily context, like below:

Can I somehow limit the output to not include 6th of Sep?

Thanks

[Anonymous]

Hi @Anonymous ,

Thanks for your reply. 

This is the sample data

Then I slice on 1 week and get result as below (stocks are summed up for each week)

10 is not a correct value for the week ending on the 5/09. It only considers 30/08 not other values.

In this case I only want to show the week ending on the 29th.

Addressing you questions:

1. Do you have a date dimension table in your data model and is the data displayed in weeks on matrix?

Yes, I have date dimension table which is connected 1:many to the table containing stock values. Both Date and Week Ending fields come from that Date table.

Data in the matrix is summed up by week.

2. You said that the table does not keep historical data, does this mean that the table only has data for the present and for future periods?

Correct

3. According to your example, if it slice on Aug 20th, is the data only displayed on the matrix as shown below?

Correct

Thanks

[amitchandak]

@Anonymous , looking for this week /last week kind of stuff?

One way is . Limit till in week in range 

var _max = minx(allselected('Date'), 'Date'[Week end date])

return

calculate([Measure], filter('Date', 'Date'[date] < _max))

refer if needed

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
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

HI @amitchandak ,

Thanks for reply.

I have replaced [Measure] in your code with a measure which I use to calculate stock. This results in blank matrix.

The aim is to have only complete weeks in the view.

Thanks