Forum Discussion

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

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

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?

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

Helper I

6 years ago

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

week_report	week_incident_open	value
6/4/2020	23/12/2019	4
6/4/2020	10/2/2020	1
6/4/2020	9/3/2020	1
6/4/2020	30/3/2020	2
13/4/2020	3/2/2020	1
13/4/2020	9/3/2020	1
13/4/2020	30/3/2020	1
13/4/2020	6/4/2020	2
20/4/2020	3/2/2020	1
20/4/2020	9/3/2020	1
20/4/2020	30/3/2020	3
20/4/2020	13/4/2020	1
20/4/2020	20/4/2020	2
27/4/2020	9/3/2020	1
27/4/2020	30/3/2020	1
27/4/2020	27/4/2020	9

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

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