Forum Discussion

dsabsi's avatar
dsabsi
Icon for Advocate I rankAdvocate I
9 years ago
Solved

How do you show your data except for the last 6 weeks?

Hello everyone,    I need your help with my Power BI dashboard.  I have plotted a bunch of data regarding the revenues per customer and I have added year + week filters (DIM_DATE).    I want to ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi dsabsi,

     

    How about this? All the [FilterColumn] = 1 is the data you want.

     

    FilterColumn =
IF ( [Date] <= TODAY () - 42, 1, 0 )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

You probably want to add a calculated column that calculates the week differential from the current week. You can use WEEKNUM and TODAY functions for this in DAX. Then you could add this column to your visual filter area and restrict it to the values you want, >5 or >6 differential.

dsabsi's avatar
dsabsi
Icon for Advocate I rankAdvocate I
9 years ago

Greg_Deckler, thanks for your swift reply. 

I tried to use your calculated solution but it didn't work for me...it shows as date 10-9-2017 which is 6 weeks ago. 

the formula: smoupre = today()-(weeknum(today();1)-6) 

 

but I want to have all the data except for 6 weeks ago... could you please be so kind to write this formula? 

 

Cheers, 

 

DSabsi

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Assuming you have a Date column:

     

    WeekNum Diff = WEEKNUM(TODAY()) - WEEKNUM([Date].[Date])

    You could then filter this to be greater than 5 or 6. Might have some Year issues to worry about (a week in 2016 is the same weeknum as a week in 2017) but this is the general idea.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi dsabsi,

     

    How about this? All the [FilterColumn] = 1 is the data you want.

     

    FilterColumn =
IF ( [Date] <= TODAY () - 42, 1, 0 )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale