Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Set default week date dimension in tabel

Hello Power BI community

I have trouble with calculating default week by using Isfiltered (im not sure this is the right way to do)

 

I want my data from my table show the lastest 10 weeks as default, however, I also allow users to use the Week Filter in the left corner and select weeks that earlier than the lastest 10 weeks.


This is my column week so far : 

Year-Week vis true = RIGHT(DIM_DATE[Year-Week vis],2)
 

 

Thanks you 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    You can try to build an unrelated table to build the week slicer and build measures to calculate the value in matrix.

    My Sample:

    YearWeek is a caluclated column.

     

    YearWeek = YEAR(Sales[Date])*100+WEEKNUM(Sales[Date],2)

     

    Slicer table:

     

    Table = VALUES(Sales[YearWeek])

     

    Build a slicer by this table and build measures to calculate the value as below.

     

    Value = 
VAR _SelectValue = VALUES('Table'[YearWeek])
VAR _CURRENTYearWeek = YEAR(TODAY())*100+WEEKNUM(TODAY(),2)
VAR _RESULT1 = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[YearWeek] in _SelectValue))
VAR _RESULT2 = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[YearWeek]>_CURRENTYearWeek-10))
RETURN
IF(ISFILTERED('Table'[YearWeek]),_RESULT1,_RESULT2)

     

    Result:

    By default my matrix will show values in last 10 weeks.

    It can show the week I select in slicer as well.

    Best Regards,

    Rico Zhou

     

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous 

    You can create a week type in you table and use that. You can default on last 10 weeks

     

    Week Type = Switch( True(),
    [start week]<=Today() && [end date]>=Today()-70,"Last 10 Week" ,
    [Week Name]
    )

     

     

    Other columns

     

    Week Start = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Number = WEEKNUM([Date],2)
    Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello amitchandak

      Thanks you for your answer but this is not what im looking for :

       

      I want an output of every single week for the last 10 weeks like this below



      And still keep my week filtered if I needed to find weeks that further than last 10 weeks.


       

       

       




  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    You can try to build an unrelated table to build the week slicer and build measures to calculate the value in matrix.

    My Sample:

    YearWeek is a caluclated column.

     

    YearWeek = YEAR(Sales[Date])*100+WEEKNUM(Sales[Date],2)

     

    Slicer table:

     

    Table = VALUES(Sales[YearWeek])

     

    Build a slicer by this table and build measures to calculate the value as below.

     

    Value = 
VAR _SelectValue = VALUES('Table'[YearWeek])
VAR _CURRENTYearWeek = YEAR(TODAY())*100+WEEKNUM(TODAY(),2)
VAR _RESULT1 = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[YearWeek] in _SelectValue))
VAR _RESULT2 = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[YearWeek]>_CURRENTYearWeek-10))
RETURN
IF(ISFILTERED('Table'[YearWeek]),_RESULT1,_RESULT2)

     

    Result:

    By default my matrix will show values in last 10 weeks.

    It can show the week I select in slicer as well.

    Best Regards,

    Rico Zhou

     

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