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

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. 🙂

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Thanks for providing data.  This turned out to be trickier than I expected.  The reason it didn't work before was that the slicer on Report Week was acting on the visual (x-axis) even though we were using All() to remove it for the measures.  To get around this, I added a DAX table called ReportDates = VALUES(tb_service_snapshot[week_report]) and made a slicer from that column.  I then made two new measures using the TREATAS() function to pass the filter from that new slicer to the measures.  One measure calculates for the selected report week and the other for the previous. 

     

    This Report Week = var selectedreportweek = values(ReportDates[week_report])
    var result = CALCULATE([sum measure], TREATAS(selectedreportweek, tb_service_snapshot[week_report]))
    return if(result =0, BLANK(), result)
     
    Last Report Week = var selectedreportweek = DATEADD(values(ReportDates[week_report]),-7,DAY)
    var result = CALCULATE([sum measure], TREATAS(selectedreportweek, tb_service_snapshot[week_report]))
    return if(result =0, BLANK(), result)

     

    Please let me know if you want the pbix file.

     

    If this is it, please mark as complete.  Kudos also appreciated.

     

    Regards,

    Pat