Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Monthly Users Chart with Measure, changing dependent on Date slicer

Power BI Chart ( Clustered Column Chart) on Direct Query

 

I have a measure that reports the number of daily users to an application . My home page on Power BI allows the Report consumer to select a day they want to view the stats for, and they get a Daily , Weekly and Monthly picture.

 

Daily Measure -

_UsersAccessing_Daily = CALCULATE(
DISTINCTCOUNTNOBLANK(AppAccessLog[UserID]),
FILTER('Date','Date'[DateId]=SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId])))

)+0

 

Currently I display the current month in a Graph, with only using a measure , _UsersAccessing_Monthly_Graph and the MonthTextShort field from the Date Table. This works lovely as I know exactly what the selected month is because my model is joined to the Date Table.

 

Monthly Measure

 

_UsersAccessing_Monthly_Graph =
VAR Selected_Date = SELECTEDVALUE ( 'DateSelector'[MonthDate], MAX ( 'DateSelector'[MonthDate] ) )
VAR Result =
CALCULATE (
DISTINCTCOUNTNOBLANK ( AppAccessLog[UserID] ),
'Date'[MonthDate] <= Selected_Date
)
RETURN
Result + 0

 

Now I want the last 3 months displayed depending on the Date Selected on the Page . How do I do this ? I cannot seem to get one to work that changes with the date changing properly ? I have tried introducing all in the above measure and take away 3 months in lots of different measures like below.

 

_UsersAccessing_Monthly_Graph=
var CurrentDate= SELECTEDVALUE('DateSelector'[DayDate])
var PreviousDATE= DATE(YEAR(CurrentDate),MONTH(CurrentDate)-3,DAY(CurrentDate))
var RESULT= CALCULATE(
DISTINCTCOUNTNOBLANK( AppAccessLog[UserID]),
FILTER (
All('Date'),
'Date'[DateId]>=PreviousDATE && 'Date'[DateId]<=CurrentDate
)
)+0
RETURN RESULT

 

My last attempt I charted DISTINCTCOUNTNOBLANK (AppAccessLog[UserID] ) against the MonthTextShort from the Date Table. This does not change with the Day Selected Slicer on my HOME page, which selects a date from a disconnected DATE table called DateSelector, as there is no relationship. All my visuals on the page and then driven off CALCULATE measures. Making the relationship betwen the DATE table and the DateSelector table may cause me some issues from previous experience so trying to not do it .

 

What is the best approach to doing this as I am running out of muster at the moment?

 

 

 

  • Anonymous's avatar
    Anonymous
    6 years ago

    The _UsersAccessing_Monthly_Graph mysteriously starts working. In business again ! Wipeee.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    The _UsersAccessing_Monthly_Graph mysteriously starts working. In business again ! Wipeee.