Forum Discussion

Amjad1002's avatar
Amjad1002
Icon for Helper III rankHelper III
3 years ago

Prior week and Current week display

Hello Folks,

 

I am trying to make two display, one should show current week and second one should show Perior week data in display.

When new data update in share point excel file such as, once week 4 data update so my display adjust automatically when I do refresh and publish. I want to see week 3 as a current week and week 2 as a prior week. When new data come for week 4 so my week will be current week and week 3 will be prior week. I have week # in my data already. https://drive.google.com/drive/folders/1qJ4ZxEP-_YSLTky0S43VCbztC0BIV65V?usp=share_link 

 

I was also looking how to upload file directly to post?

Thanks

 

 

 

Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Amjad1002 , Based on today

     

    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))

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

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

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

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

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

     

     

     

    Based on selected dates, you need column in date table

     

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

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('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))

     

    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
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    • Amjad1002's avatar
      Amjad1002
      Icon for Helper III rankHelper III
      3 years ago

      Hey Amit,

      I have week number in my data and that week I have to use not any date range due to some reason. 

      I am not quite sure that I need to still follow above steps?