cancel
Showing results for
Did you mean:
Frequent Visitor

## Measures & Filters

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.

1 ACCEPTED SOLUTION
Community Champion

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!

Thank you for the kudos 🙂

Proud to be a Super User!

6 REPLIES 6
Community Champion

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!

Thank you for the kudos 🙂

Proud to be a Super User!

Frequent Visitor

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.

Community Champion

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!

Thank you for the kudos 🙂

Proud to be a Super User!

Frequent Visitor

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?

Community Champion
You would have to add the variables and the filters to every measure, which I assume is less than ideal

Alternatively you could use bookmarks to change the filter on the calendar date, if you use relative filters that may actually work quite OK, but then you need buttons, not a slicer
https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks#bookmarks-for-shapes-and-images

Thank you for the kudos 🙂

Proud to be a Super User!

Frequent Visitor

Thanks Stachu,
Appreciate your prompt response and update

Announcements