Forum Discussion

gabrielvigo's avatar
gabrielvigo
Helper I
6 years ago

Show Current and Last Week values into chart

Hi all

I have a table with three fields. "week_report","week_incident_open" and "value".

 

I need show into a bar chart a SUM(value) of the SELECTEDVALUE(week_report). When "week_incident_open" is the X axis. For that, no problem, because is a simple SUM.

For example, in this table. I have SUM(value) = 11 for the 27/4 week_report distributed by "week_incident_open".

 

When I select into week_report = 27/4

Axis X= week_incident_open

Value= SUM(value)

But into a same chart, I need show the value about the last week.

For example, in this case, the last week is 20/4 and I have SUM(value) = 7  distributed by "week_incident_open".

 

So, I need show the data into a same chart. One bar for the current week and one for the previous week.

I was able to do it to a measure, but it doesn't apply to the graph. I think the problem there is the "week_incident_open" field.

 

😕

The measure does not distribute the values ​​across the "week_incident_open" field.

 

Measure 

med_value_lastwwek =
VAR LastWeekNumber = WEEKNUM(SELECTEDVALUE(TB_SERVICE_SNAPSHOT[week_report])) - 1
RETURN
CALCULATE(SUM(TB_SERVICE_SNAPSHOT[value]);FILTER(ALL(TB_SERVICE_SNAPSHOT);WEEKNUM(TB_SERVICE_SNAPSHOT[week_report]) = LastWeekNumber))
 

I hope you understand and can help me.

Need Help

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    The All() in your measure is also removing the filter on week_incident_open.  Try this variation:

     

    med_value_lastwwek =
    VAR LastWeekNumber = WEEKNUM(SELECTEDVALUE(TB_SERVICE_SNAPSHOT[week_report])) - 1
    RETURN
    CALCULATE(SUM(TB_SERVICE_SNAPSHOT[value]); FILTER(ALLEXCEP(TB_SERVICE_SNAPSHOT, TB_SERVICE_SNAPSHOT[week_incident_open]);WEEKNUM(TB_SERVICE_SNAPSHOT[week_report]) = LastWeekNumber))
     
    You had attached an image of the data, so I couldn't copy/paste into PBI to easily try it out.  Please let me know if it doesn't work or if any questions.  If it does, please mark it as solution.
    Regards,
    Pat
     
    • gabrielvigo's avatar
      gabrielvigo
      Helper I
      6 years ago

      Hi!

       

      As a measure, the total is fine.

      But inside the chart it only shows me the field on day (week_incident_open) that are the same I think.

      The current and the last week I had incidents on 30/03. And only on that day it shows me values ​​(3).

      I need that show me all of the last week too.

      Do you think it can?

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        In this case, less may be more.  Please try this one.  If not, please paste the values from your image so I can try it out directly.

         

        med_value_lastwwek =
        VAR LastWeekDate = SELECTEDVALUE(TB_SERVICE_SNAPSHOT[week_report])-7
        RETURN
        CALCULATE(SUM(TB_SERVICE_SNAPSHOT[value]);TB_SERVICE_SNAPSHOT[week_report] = LastWeekDate)
         
        This way, you are only replacing the filter on [week_report].
         
        Regards,
        Pat
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    gabrielvigo , Refer to my blog on week . I have used week rank to deal with it

    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

     

    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    Refer this webinar on same : https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184