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

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