Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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...
Solved! Go to 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.
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.
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!
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...
User | Count |
---|---|
82 | |
72 | |
67 | |
47 | |
36 |
User | Count |
---|---|
111 | |
56 | |
50 | |
42 | |
40 |