cancel
Showing results for
Did you mean:
Regular Visitor

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

1 ACCEPTED SOLUTION
Community Support

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!

3 REPLIES 3
Community Support

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!

Super User

Hi @Sherin89
Would you please share a sample file?

Community Support

This post is not related to Issue, so transfer it to respective forum to get further help.

Best Regards,

Community Support Team _ Caiyun

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors