Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Display Last X Week Data Dynamically Based On 'To' Date Selection

Hi everyone,   We have requirement in our project such that based on the 'To' date selected in the period selector, we need to show last 4 weeks data alone in the chart. Supposing we are at the 47t...
Need Help
  • edhans's avatar
    edhans
    7 years ago

    Ideally you would filter this data out before it gets loaded into Power BI via the Power Query engine, but you mentioned that your data is coming from a cube, so if it is SSAS, then you cannot use Power Query.

     

    You have two possible option (there are probably a dozen ways to go about it, but these two are straightforward):

    1. Use a calculated column to define how many weeks ago a date is, then add that column to a filter (report, page, or visual level) and only show those that are <=4. This would calculate that value:
      Weeks Ago = WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2)
    2. Create a calculated table that just had the relevant data, then use that table in your visual. This measure/table would do that:
      4 Week History = 
CALCULATETABLE(
    Sales,
    FILTER(Sales,WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2) <= 4)
)
edhans's avatar
edhans
Community Champion
7 years ago

Ideally you would filter this data out before it gets loaded into Power BI via the Power Query engine, but you mentioned that your data is coming from a cube, so if it is SSAS, then you cannot use Power Query.

 

You have two possible option (there are probably a dozen ways to go about it, but these two are straightforward):

  1. Use a calculated column to define how many weeks ago a date is, then add that column to a filter (report, page, or visual level) and only show those that are <=4. This would calculate that value:
    Weeks Ago = WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2)
  2. Create a calculated table that just had the relevant data, then use that table in your visual. This measure/table would do that:
    4 Week History = 
CALCULATETABLE(
    Sales,
    FILTER(Sales,WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2) <= 4)
)