Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtering Date + 7 Days

Hi,   I was wondering if there was a way to create a column or filter so that if you select a specific date, it shows that given date plus a week, or 7 days for the rest of the year/given years. S...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Please check the measure below and see if the result achieve your expectation.

    Measure = 
var sv = SELECTEDVALUE('CALENDAR'[Date])
return 
IF(MAX('Table'[Date])<sv,BLANK(),IF(MOD(DATEDIFF(sv,MAX('Table'[Date]),DAY),7)=0,MAX('Table'[Date])))

     

    BTW, Pbix as attached, hopefully works for you.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    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
6 years ago

In Excel, for example, if column A1 had the value of "1/1" and I type "A1+7" in column A2, this would result in "1/8". Doing this for the rest of column A for a whole year would give me data from "1/1" every week 

If I were to change A1 to "1/2" this would result in A2 to be "1/9" and so forth for the rest of the column A 

I am trying to do this in BI, but I am unsure how to correctly filter or calculate a column so that if I were to chose one date, it would filter for the rest of the column for 1 week at a time

 

Thank you!
Sarah

parry2k's avatar
parry2k
Super User
6 years ago

Anonymous still not sure what the end goal is ? you want to apply this in a measure, filter a visual/page, what this calculation is going to do? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    In the end this would show weekly data for a report that was done in Tableau, but will be shifted to BI... 

    also this report breaks weekly totals down by regions of DC, MD, and VA

    Overall, this report has been done in Excel and Tableau, but there is a shift of resources to Power BI 

     

    Thank you !
    Sarah 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Please check the measure below and see if the result achieve your expectation.

      Measure = 
var sv = SELECTEDVALUE('CALENDAR'[Date])
return 
IF(MAX('Table'[Date])<sv,BLANK(),IF(MOD(DATEDIFF(sv,MAX('Table'[Date]),DAY),7)=0,MAX('Table'[Date])))

       

      BTW, Pbix as attached, hopefully works for you.

       

      Best Regards,

      Jay

      Community Support Team _ Jay Wang

      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
        6 years ago

        amazing thank you so much!
        Sarah