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 ,

 

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

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

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

    Hi Anonymous ,

     

    Are you still looking into this?

     

    Thanks