Forum Discussion

AnnaML's avatar
AnnaML
Frequent Visitor
5 years ago
Solved

Show Latest and future Weeks

Hello,  I have a bar chart showing hours per week actuals vs target. Now It shows latest 20 weeks, i want the chart to show the latest 20 weeks but also the future 4 weeks. This in order to see wh...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi AnnaML ,

     

    You could create  a new table with Week column(with Year*100+WeekNum format) for slicer,and use the following formula to create a measure:

    Week =
YEAR ( [Date] ) * 100
    + WEEKNUM ( [Date], 1 )
    Measure =
IF (
    MAX ( 'Table'[Week] )
        >= SELECTEDVALUE ( ForSlicer[Week] ) - 20
        && MAX ( 'Table'[Week] )
            <= SELECTEDVALUE ( ForSlicer[Week] ) + 4,
    1,
    0
)

    Then apply it to visual-level filter,set as "is 1" like this:

    Please take a look at the pbix file here.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi AnnaML ,

 

You could create  a new table with Week column(with Year*100+WeekNum format) for slicer,and use the following formula to create a measure:

Week =
YEAR ( [Date] ) * 100
    + WEEKNUM ( [Date], 1 )
Measure =
IF (
    MAX ( 'Table'[Week] )
        >= SELECTEDVALUE ( ForSlicer[Week] ) - 20
        && MAX ( 'Table'[Week] )
            <= SELECTEDVALUE ( ForSlicer[Week] ) + 4,
    1,
    0
)

Then apply it to visual-level filter,set as "is 1" like this:

Please take a look at the pbix file here.

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AnnaML's avatar
AnnaML
Frequent Visitor
5 years ago

Thank you so much for your time and effort! It helped me!