Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a project currently I am working on right now, and I am struggling to get this methodology to work.
For instance, end user selects a date, based on the date selection, it calculates
1. Date diff between the selected date and “Referral date”
2. Calculates the weeks based on the step1.
3. Group step 1 ( so it’s the week falls <10 then group 0-10 week, >=10 && <20 then 10-20 etc) I am particularly struggling around the groups which I am trying to do it in a column as Id need to create another column to sort the Week order and need to show this is a clustered chart.
What I really want to do is this.
Any help would be appreciated.
Solved! Go to Solution.
Hi @Anonymous
You may try this workaround.
1 Create a Calendar Table with specified range of date and use date column in this table to create Slicer.
calendar = CALENDAR(DATE(2021,7,31),DATE(2099,4,15))
2 Calculate weeks range based on the selected date in Slicer. Measures are as follows.
In Days =
VAR selectedDate =
MIN ( 'calendar'[Date] )
RETURN
DATEDIFF ( MAX ( Auditbase[ClockStartDate] ), selectedDate, DAY )
)
In Weeks = Auditbase[In Days]/7
Weeks =
VAR absInWeeks =
ABS ( Auditbase[In Weeks] )
VAR up =
ROUNDUP ( absInWeeks, 0 )
VAR down =
ROUNDDOWN ( absInWeeks, 0 )
RETURN
IF (
up = 0
&& down = 0,
0 & "-" & 1 & "Weeks",
IF ( up = down, ( down - 1 ) & "-" & up & "Weeks", down & "-" & up & "Weeks" )
)
3 Create a WeekRangeTable by making use of Calendar Table. Calculated table and Calculated columns are as follows.
Calculated Table:
WeekRangeList =
VAR MaxDate =
MAX ( 'calendar'[Date] ) / 7
RETURN
GENERATESERIES ( 0, MaxDate, 1 )
Calculated columns:
WeekBehind = 'WeekRangeList'[Value]+1
Week = 'WeekRangeList'[Value]&"-"&'WeekRangeList'[WeekBehind]&"Weeks"
WeekRangTable will look like this.
At last, create a Measure to dynamically count the weeks.
CountOfWeeks =
VAR WeekFromAuditbase =
SELECTCOLUMNS (
ADDCOLUMNS ( ALLSELECTED ( Auditbase[ClockStartDate] ), "weeks", [Weeks] ),
"Weeks", [weeks]
)
VAR WeekFromWeekRangeList =
SELECTCOLUMNS (
FILTER ( 'WeekRangeList', 'WeekRangeList'[Week] IN WeekFromAuditbase ),
"Week", [Week]
)
RETURN
COUNTX ( FILTER ( WeekFromAuditbase, [Weeks] IN WeekFromWeekRangeList ), [Weeks] )
Then, the result will look like this.
Also, attached the pbix file as reference.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Hi @Anonymous
You may try this workaround.
1 Create a Calendar Table with specified range of date and use date column in this table to create Slicer.
calendar = CALENDAR(DATE(2021,7,31),DATE(2099,4,15))
2 Calculate weeks range based on the selected date in Slicer. Measures are as follows.
In Days =
VAR selectedDate =
MIN ( 'calendar'[Date] )
RETURN
DATEDIFF ( MAX ( Auditbase[ClockStartDate] ), selectedDate, DAY )
)
In Weeks = Auditbase[In Days]/7
Weeks =
VAR absInWeeks =
ABS ( Auditbase[In Weeks] )
VAR up =
ROUNDUP ( absInWeeks, 0 )
VAR down =
ROUNDDOWN ( absInWeeks, 0 )
RETURN
IF (
up = 0
&& down = 0,
0 & "-" & 1 & "Weeks",
IF ( up = down, ( down - 1 ) & "-" & up & "Weeks", down & "-" & up & "Weeks" )
)
3 Create a WeekRangeTable by making use of Calendar Table. Calculated table and Calculated columns are as follows.
Calculated Table:
WeekRangeList =
VAR MaxDate =
MAX ( 'calendar'[Date] ) / 7
RETURN
GENERATESERIES ( 0, MaxDate, 1 )
Calculated columns:
WeekBehind = 'WeekRangeList'[Value]+1
Week = 'WeekRangeList'[Value]&"-"&'WeekRangeList'[WeekBehind]&"Weeks"
WeekRangTable will look like this.
At last, create a Measure to dynamically count the weeks.
CountOfWeeks =
VAR WeekFromAuditbase =
SELECTCOLUMNS (
ADDCOLUMNS ( ALLSELECTED ( Auditbase[ClockStartDate] ), "weeks", [Weeks] ),
"Weeks", [weeks]
)
VAR WeekFromWeekRangeList =
SELECTCOLUMNS (
FILTER ( 'WeekRangeList', 'WeekRangeList'[Week] IN WeekFromAuditbase ),
"Week", [Week]
)
RETURN
COUNTX ( FILTER ( WeekFromAuditbase, [Weeks] IN WeekFromWeekRangeList ), [Weeks] )
Then, the result will look like this.
Also, attached the pbix file as reference.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Hi @Anonymous
Would you please share a sample file?
Hi @Anonymous
This post is not related to Issue, so transfer it to respective forum to get further help.
Thanks for your understanding!
Best Regards,
Community Support Team _ Caiyun
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |