Show Current and Last Week values into chart

[gabrielvigo]

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.

[amitchandak]

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

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

[mahoneypat]

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]

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]

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]

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]

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