Help to show the last 12 weeks of rolling data in a line chart

[lennox25]

Hi, 

I have a line chart and I only need it to show the last rolling 2 weeks of data. I have put a date filter on (using the date table) and the line chart X-axis used week no and period no from the sales table. Its pulling through incorrectly in terms of showing zeros for anything beyond 12 weeks when I want it to start on the line chart from the week 202335 (end of 202308) How can I make this work?

[amitchandak · Accepted solution]

@lennox25 , if you have selected 12 weeks and it shows 0 for all other weeks, means you are using +0 or coalesce or blank handling in your measure avoid that.

In case you are adding +0 for in between range data try like

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1)

[v-yangliu-msft · Accepted solution]

Hi  @lennox25 ,

I created some data:

Here are the steps you can follow：

1. Create slicer table.

Slicer =
DISTINCT('Table'[year_week])

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Slicer'[year_week])
var _maxdate=MAXX(FILTER(ALL('Table'),'Table'[year_week]=_select),[Date])
var _mindate=DATE(YEAR(_maxdate),MONTH(_maxdate)-3,DAY(_maxdate))
return
IF(
    MAX('Table'[Date])>=_mindate&&MAX('Table'[Date])<=_maxdate,SUMX('Table','Table'[rand]),BLANK())

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak · Accepted solution]

@lennox25 , if you have selected 12 weeks and it shows 0 for all other weeks, means you are using +0 or coalesce or blank handling in your measure avoid that.

In case you are adding +0 for in between range data try like

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1)