Forum Discussion
Helper IINumber 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...
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- lbendlin
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sander1401THANKS!
it didn't work in the beginning, but I changed the -15 inVAR _minimal_weeks_offset = _maximal_weeks_offset - 15
into - SELECTEDPERIOD and now it works!
