Forum Discussion
calculate total days from a selected date
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.
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]/7Weeks = 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]+1Week = '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!
3 Replies
v-cazheng-msftCommunity Support
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
- tamerj1
Community Champion
Hi Anonymous
Would you please share a sample file? - v-cazheng-msft
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]/7Weeks = 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]+1Week = '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!
