Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Relative Date Slicer

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 dat...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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