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...
Need Help
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
      • gabrielvigo's avatar
        gabrielvigo
        Helper I
        6 years ago

        That measure didn't work for me.
        I copy a table with the sample data.

         

        week_reportweek_incident_openvalue
        6/4/202023/12/20194
        6/4/202010/2/20201
        6/4/20209/3/20201
        6/4/202030/3/20202
        13/4/20203/2/20201
        13/4/20209/3/20201
        13/4/202030/3/20201
        13/4/20206/4/20202
        20/4/20203/2/20201
        20/4/20209/3/20201
        20/4/202030/3/20203
        20/4/202013/4/20201
        20/4/202020/4/20202
        27/4/20209/3/20201
        27/4/202030/3/20201
        27/4/202027/4/20209

         

        I copy a table with the sample data. I hope you can help me. 🙂