calculate total days from a selected date

[Sherin89]

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. 

[v-cazheng-msft · Accepted solution]

Hi @Sherin89 

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!

[tamerj1]

Hi @Sherin89 
Would you please share a sample file? 

[v-cazheng-msft]

Hi @Sherin89 

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