Forum Discussion

eduardosilvin3's avatar
2 years ago
Solved

Weekly filters

Helllo all, I need to add filter with the date (year, month and day) for my 4 graphs: Daily, previous day, current week, previous week.

So picking 15th november 2023 for example.

The results I need in the graphs:

Daily  - nov 15 results

previous day - nov 14 results

Week to date – because our weeks start on a Monday it would be nov 13 to nov 19 results (15 because is the last day)

previous week  – nov 6 to nov 12 results

  • eduardosilvin3 , You can use these measures

     

    Based on today, use all ('Date'), in case you want remove any other filter from date

     

    Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Today() ) )
    Yesterday = CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-1 ) )
    Same day Last week Today= CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-7 ) )

     



    Based on filter or row context

    This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
    Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
    Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format


    These measures can help
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
    LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

    WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
    LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))



     

     

    WTD Today =
    var _min = TODAY() -WEEKDAY(TODAY(),2) +1 //Monday week start
    var _max = today()
    return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    LWTD Today =
    var _min = TODAY() -WEEKDAY(TODAY(),2) -6 //Monday week start
    var _max = today() -7
    return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    Cheat Sheet — Power BI Time Intelligence Formulas Using Today
    https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27304

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8
    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

2 Replies

  • eduardosilvin3 , You can use these measures

     

    Based on today, use all ('Date'), in case you want remove any other filter from date

     

    Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Today() ) )
    Yesterday = CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-1 ) )
    Same day Last week Today= CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-7 ) )

     



    Based on filter or row context

    This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
    Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
    Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format


    These measures can help
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
    LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

    WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
    LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))



     

     

    WTD Today =
    var _min = TODAY() -WEEKDAY(TODAY(),2) +1 //Monday week start
    var _max = today()
    return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    LWTD Today =
    var _min = TODAY() -WEEKDAY(TODAY(),2) -6 //Monday week start
    var _max = today() -7
    return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    Cheat Sheet — Power BI Time Intelligence Formulas Using Today
    https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27304

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8
    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

    • eduardosilvin3's avatar
      eduardosilvin3
      Icon for Helper II rankHelper II

      So sorry for the delay, I was in another projects! I followed with difficulties, but I found your PBI file and WOW, life saver! I saved it in my Onedrive as "Dates YOU NEED THIS" in case someday I forget about it! thanks again!