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 date for aggregation.

 

The matrix looks like this

 

 WeekEnding 
Material23.0829.08
AStock levels 
BStock levels 
CStock levels 
DStock 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

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

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

      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

  • 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

    • 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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