DAX measure to produce Weekly Chart

Question

Looking to produce a weekly chart with day on x axis and a measure on y axis called Repeated Time to Access . I can get the measure to calculate for a chosen day but not the week.

This is not real data but some mock up data I took ages to prepare, to protect some sensitive data.

Figure 1 – What Page in Power BI looks like ?

The value for Mon should be 569,997. The values for Wed, Thurs and rest of the week should be 0. Figure 2 - Mondays Values

So there the measure that does not work is this one :

_RepeatTimeToAccess_Value_Days_Graph =
VAR Selected_Date =
    SELECTEDVALUE ( 'DateSelector'[WeekDate], MAX ( 'DateSelector'[WeekDate] ) )
VAR allseconds =
    CALCULATE (
        AVERAGEX (
            VALUES ( AppUser[AppUserId] ),
            DATEDIFF (
                IF ( AppUser[LastLogin] = DATE ( 1899, 12, 30 ), BLANK (), AppUser[LastLogin] ),
                AppUser[CurrentDate],
                SECOND
            )
        ),
        'Date'[WeekDate] = Selected_Date
    )
VAR days =
    INT ( allseconds / 24 / 60 / 60 )
RETURN
    allseconds

There are two measures ( Current Date And Last Login) I include that are referenced also:

CurrentDate =
VAR DateSelected =
    CALCULATE (
        MAX ( 'Date'[DayDate] ),
        FILTER (
            'Date',
            SELECTEDVALUE ( 'DateSelector'[DayDate], MAX ( 'DateSelector'[DayDate] ) ) = 'Date'[DayDate]
        )
    )
VAR Today =
    TODAY ()
VAR Result =
    IF (
        Today = DateSelected,
        NOW (),
        DATEVALUE ( DateSelected ) + TIME ( 23, 59, 59 )
    )
RETURN
    Result

LastLogin =
CALCULATE (
    MINX (
        TOPN ( 1, 'AppAccessLog', 'AppAccessLog'[Source_OriginalTimeStamp] ),
        'AppAccessLog'[Source_OriginalTimeStamp]
    ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[DateId]
            <= SELECTEDVALUE ( 'DateSelector'[DateId], MAX ( 'DateSelector'[DateId] ) )
    )
) + 0

Average Repeated Time to access for all users regardless if they logged in today. The above measure excludes any user that never logged in by the date exclusion if statment. This works perfectly for an individual day selected using a disconnected Date Selector table on the HOME power BI page.

Dashboard on Direct Query mode, so relevant to dax options needed please.

To explain this currentdate measure, I for today take the time right now as the current date if today is selected. If it yesterday the measure is being calculated for then it is yesterday date at 23.59.59 that will be used as the current date.

DAX measure to produce Weekly Chart

0 Answers