Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |