cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsherlock
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:

 

image.png

 

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.

 

image.png

 

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
Stachu
Community Champion
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
)
)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Stachu
Community Champion
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
)
)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

rsherlock
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.

Stachu
Community Champion
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
        )
    )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

rsherlock
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?

again appreciate your help

 

Stachu
Community Champion
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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

rsherlock
Frequent Visitor

Thanks Stachu,
Appreciate your prompt response and update

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors