Number of weeks in graph, via datefilter or parameter

[Sander1401]

Hi all,

I am constructing a barchart with on the x-axis weeks and on the Y-axis sales.
I also use a datefilter, to filter the period of sales.
Unfortunately I am using text format of weeks. So at this moment my period is too big for the visual, and the newest weeks are hidden by default (the user must slide to the right)
My aim is to make an X-axis that reacts on the datefilter (that works), but also on a parameter which a user can use to say how many weeks have to be shown.
For example, the user wants to see 15 weeks, and he/she sets the datefilter on min 2022-01-01 and max 2024-06-30, it only has to show 15 weeks as of 2024-06-30.
I've tried this with weeks offset, and I can calculate the maximal weeks offset (diff between today en 2024-06-30) and minimal weeks offset (maximal weeks offset - 15). But I can't make it work as a filter on the barchart....
Can anyone help me out on the dax formula with this?
I've tried something like this:
if(datetable[weeks offset] <= 'maximal weeks offset'  && datetable[weeks offset] >= 'minimal weeks offset', 1, 0)
but that doesn't work, since it looks per line in stead of the whole table.
I've tried much more, but nothing worked...

[Anonymous · Accepted solution]

Hi @Sander1401 ,

Here I create a sample to have a test. I think need to use ALL() or ALLSELECTED() in your calculation.

DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month Number",MONTH([Date]),"WeekStart",[Date] - WEEKDAY([Date],2) + 1,"WeekNum",WEEKNUM([Date],2))

Measure:

Measure = 
VAR _SELECTPERIOD = SELECTEDVALUE('Week Period'[Week Period])
VAR _MAXDATE = IF(TODAY()>MAX(DimDate[Date]),MAXX(ALLSELECTED(DimDate),DimDate[Date]),TODAY())
VAR _maximal_weeks_offset = CALCULATE(MAX(DimDate[WeekRank]), FILTER(ALLSELECTED(DimDate),DimDate[Date] = _MAXDATE))
VAR _minimal_weeks_offset = _maximal_weeks_offset - 15
VAR _MINDATE = CALCULATE(MIN(DimDate[Date]),FILTER(ALLSELECTED(DimDate),DimDate[WeekRank] = _minimal_weeks_offset))
RETURN
IF(MAX(DimDate[Date]) >= _MINDATE && MAX(DimDate[Date])<=_MAXDATE,1,0)

Result is as below.

Best Regards,
Rico Zhou

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

[Sander1401]

THANKS!

it didn't work in the beginning, but I changed the -15 in 

VAR _minimal_weeks_offset = _maximal_weeks_offset - 15

into - SELECTEDPERIOD and now it works!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...