Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Sander1401
Helper II
Helper II

Number of weeks in graph, via datefilter or parameter

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vrzhoumsft_0-1734938161287.png

 

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.

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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.

vrzhoumsft_0-1734938161287.png

 

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.

 

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
Super User
Super User

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.