Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I’m pretty new to Power bi and am hoping someone can help with the following:
I am trying to build a report based on a “hard coded” set of choices, specifically:
On my main data table “Activity”, I have an “Activity Date”, the date an Activity was created. Based on the selection for Time frame (period), as above. I want all the other visuals on the page, graphs and count of activities for example to update based on this choice.
So using todays date “26/07/2019” and the Time frame (period) of “Today / yesterday I’d like a table on my visual to show all activities with an Activity date = to “26/07/2019” or an Activity date = to “25/07/2019”.
Using todays date “26/07/2019” and the Time frame (period) of “This month / last month” I’d like a table on my visual to show all activities with an Activity date where the month = “07” or an Activity date where the month = “06”
Using todays date “26/07/2019” and the Time frame (period) of “This year / last year” I’d like a table on my visual to show all activities with an Activity date where the year = “2019” or an Activity date where the year = “2018”
I have created measures to calculate the dates to be used as filters:
Yesterday = Date(year('Measure Table'[TodaysDate]),Month('Measure Table'[TodaysDate]),Day('Measure Table'[TodaysDate])-1)
Last Month = Date(year('Measure Table'[TodaysDate]),Month('Measure Table'[TodaysDate])-1,Day('Measure Table'[TodaysDate]))
Last Year = Date(year('Measure Table'[TodaysDate])-1,Month('Measure Table'[TodaysDate]),Day('Measure Table'[TodaysDate]))
TodaysDate = TODAY()
And I have attempted to make a measure based on the above :
TargetPeriod = CALCULATE(COUNT(Activity[ActivityId]),FILTER(Activity,Activity[Activity Date]=[Yesterday] && Activity[Activity Date]=[TodaysDate]))
But thats as far as I have got, cant successfully apply TargetPeriod to my PBI and not sure if I’ve gone down the best route with this.
I am struggling now, all help appreciated
Thanks.
Solved! Go to Solution.
I think it would be easier if your table had following format:
Time frame (period) | Start Date | End Date |
This Month/Last Month | 01/06/2019 | 31/07/2019 |
This Year/Last Year | 01/01/2018 | 31/12/2019 |
Today/Yesterday | 25/07/2019 | 26/07/2019 |
you can calculate table like this in DAX
SlicerTable = VAR __Today = TODAY () VAR __Year = YEAR ( __Today ) VAR __Month = MONTH ( __Today ) VAR __PreviousMonthToday = EDATE ( __Today, -1 ) VAR __PreviousMonthStart = DATE ( YEAR ( __PreviousMonthToday ), MONTH ( __PreviousMonthToday ), 1 ) RETURN UNION ( ROW ( "Time frame (period)", "Today/Yesterday", "SortOrder", 1, "StartDate", TODAY () - 1, "EndDate", TODAY () ), ROW ( "Time frame (period)", "This Month/Last Month", "SortOrder", 2, "StartDate", __PreviousMonthStart, "EndDate", EOMONTH ( __Today, 0 ) ), ROW ( "Time frame (period)", "This Year/Last Year", "SortOrder", 3, "StartDate", DATE ( __Year - 1, 1, 1 ), "EndDate", DATE ( __Year, 12, 31 ) ) )
I added the sort column so you can setup custom sort consistent with time hierarchy day>month>year. It's a bit complex but is independent of TODAY being e.g. in Jan where subtracting 1 from month gives 0 instead of 12
then you could do the following:
TargetPeriod =
VAR __StartDate = SELECTEDVALUE(SlicerTable[StartDate], BLANK())
VAR __EndDate = SELECTEDVALUE(SlicerTable[EndDate], BLANK())
CALCULATE(
COUNT(Activity[ActivityId]),
FILTER(Activity,
Activity[Activity Date] >= __StartDate
&& Activity[Activity Date] <= __EndDate
)
)
Proud to be a Datanaut!
I think it would be easier if your table had following format:
Time frame (period) | Start Date | End Date |
This Month/Last Month | 01/06/2019 | 31/07/2019 |
This Year/Last Year | 01/01/2018 | 31/12/2019 |
Today/Yesterday | 25/07/2019 | 26/07/2019 |
you can calculate table like this in DAX
SlicerTable = VAR __Today = TODAY () VAR __Year = YEAR ( __Today ) VAR __Month = MONTH ( __Today ) VAR __PreviousMonthToday = EDATE ( __Today, -1 ) VAR __PreviousMonthStart = DATE ( YEAR ( __PreviousMonthToday ), MONTH ( __PreviousMonthToday ), 1 ) RETURN UNION ( ROW ( "Time frame (period)", "Today/Yesterday", "SortOrder", 1, "StartDate", TODAY () - 1, "EndDate", TODAY () ), ROW ( "Time frame (period)", "This Month/Last Month", "SortOrder", 2, "StartDate", __PreviousMonthStart, "EndDate", EOMONTH ( __Today, 0 ) ), ROW ( "Time frame (period)", "This Year/Last Year", "SortOrder", 3, "StartDate", DATE ( __Year - 1, 1, 1 ), "EndDate", DATE ( __Year, 12, 31 ) ) )
I added the sort column so you can setup custom sort consistent with time hierarchy day>month>year. It's a bit complex but is independent of TODAY being e.g. in Jan where subtracting 1 from month gives 0 instead of 12
then you could do the following:
TargetPeriod =
VAR __StartDate = SELECTEDVALUE(SlicerTable[StartDate], BLANK())
VAR __EndDate = SELECTEDVALUE(SlicerTable[EndDate], BLANK())
CALCULATE(
COUNT(Activity[ActivityId]),
FILTER(Activity,
Activity[Activity Date] >= __StartDate
&& Activity[Activity Date] <= __EndDate
)
)
Proud to be a Datanaut!
Good afternoon Stachu,
Thank you for your repsonse and guidance.
I have been able to complete the initial steps you have suggested and created my slicer table with the appropriate start & end dates based on the three filters available. However, I am strugglig to understand where I create the "TargetPeriod" element you have documented.
Ive tried creating it as a new measure and am hitting the following error, every time..
The syntax for 'CALCULATE' is incorrect. (DAX(VAR __StartDate = SELECTEDVALUE(SlicerTable[StartDate], BLANK())VAR __EndDate = SELECTEDVALUE(SlicerTable[EndDate], BLANK())CALCULATE(COUNT(Activity[ActivityId]),FILTER(Activity,Activity[Activity Date] >= __StartDate && Activity[Activity Date] <= __EndDate)))).
Again, all help & guidance appreciated.
my mistake I forgot to add return, it should be
TargetPeriod = VAR __StartDate = SELECTEDVALUE ( SlicerTable[StartDate], BLANK () ) VAR __EndDate = SELECTEDVALUE ( SlicerTable[EndDate], BLANK () ) RETURN CALCULATE ( COUNT ( Activity[ActivityId] ), FILTER ( Activity, Activity[Activity Date] >= __StartDate && Activity[Activity Date] <= __EndDate ) )
Proud to be a Datanaut!
ah ha, Thanks Stachu, appreciate your help.
That has worked nicely 🙂
Is it possible to use the following in someway as a filter for the whole report, ie. all my visuals etc are based on data only available after that filter has been set, specifically:
TargetPeriod = VAR __StartDate = SELECTEDVALUE ( SlicerTable[StartDate], BLANK () ) VAR __EndDate = SELECTEDVALUE ( SlicerTable[EndDate], BLANK () )
Or do I have to work with this on every visual in somehow?
again appreciate your help
Thanks Stachu,
Appreciate your prompt response and update
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |