Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Current Week versus Last Week Totals as a Bar Chart

Current Week 1/9/2022 - 1/15/2022

Last Week 1/2/2022 - 1/8/2022

 

Is it possible to show this as a bar chart ?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ~

     

    I created a sample:

    Please try these measures:

     

    Current Week = 
var __Today = TODAY()
var __WeekStartDate = __Today-WEEKDAY(__Today,1)+1
var __WeekEndDate = __WeekStartDate+6
return
CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),__WeekStartDate<='Table'[Closed] && __WeekEndDate>='Table'[Closed]))

     

     

    Last Week = 
var __Today = TODAY()
var __WeekStartDate = __Today-WEEKDAY(__Today,1)-6
var __WeekEndDate = __WeekStartDate+6
return
CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),__WeekStartDate<='Table'[Closed] && __WeekEndDate>='Table'[Closed]))

     

    create a table:

    create a measure:

     

    Measure Value = 
SWITCH(
SELECTEDVALUE('Table1'[order]),
1,[Current Week],
2,[Last Week]
)

     

    Finally create a bar chart:

     

    Hope it helps!

     

    Best regards,

    Community Support Team  Gao

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

     

11 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

    Can you please elaborate it further with expected out put and provide dummy data 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

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

    Anonymous , Create these new columns in date table

     

    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    Then try measures 

    measures
    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

     

     

    Or share data in text format as suggested by mh2587 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I'm getting the following error message:

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ~

     

    I created a sample:

    Please try these measures:

     

    Current Week = 
var __Today = TODAY()
var __WeekStartDate = __Today-WEEKDAY(__Today,1)+1
var __WeekEndDate = __WeekStartDate+6
return
CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),__WeekStartDate<='Table'[Closed] && __WeekEndDate>='Table'[Closed]))

     

     

    Last Week = 
var __Today = TODAY()
var __WeekStartDate = __Today-WEEKDAY(__Today,1)-6
var __WeekEndDate = __WeekStartDate+6
return
CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),__WeekStartDate<='Table'[Closed] && __WeekEndDate>='Table'[Closed]))

     

    create a table:

    create a measure:

     

    Measure Value = 
SWITCH(
SELECTEDVALUE('Table1'[order]),
1,[Current Week],
2,[Last Week]
)

     

    Finally create a bar chart:

     

    Hope it helps!

     

    Best regards,

    Community Support Team  Gao

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It look like the Closed (2022 Redwood Incidents) does not have Date associated with it. How can I resolve this issue?

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    The definition of a week itself is unclear.  For the year 2022, when will the week start from - January 1 or the first Monday of January?  If you can create a week number column in the Calendar table and share the download link of your PBI file, I can help.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

       

      Hope this helps as I couldn't get the export form my desktop.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi~ Anonymous 
        Please check the data type of the Order column.If it is in Text format, change it to Whole number format.Then check  the bar chart.

        If the problem is not solved, please provide the error information, it will help me to find the problem, thank you very much!

        Best regards,

        Community Support Team  Gao

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