Using a Relative Time Stamp (MM/DD/YYYY HH:MM:SS) filter to set start and end fiscal month?

[Anonymous]

Hi Folks,

I currently have a date/time dimension that spits out a timestamp in (MM/DD/YYYY HH:MM:SS) format. The timestamp is compatible with the Power BI relative date filter/slider - works excellent.

However, what I would LIKE to do ideally is to substitute the date slider (which allows you to start a start/end range using MM/DD/YYYY) with something more user-friendly, such as start fiscal month and end fiscal month.

Two issues -

1) The relative slider is not compatible with fiscal months, meaning I cannot get it to display at the month level - only at the individual day level.

2) I am not sure how I would even go about implementing a new filter that uses start/end date. Most of my measures are already build, and it would be a tremendous amount of work to go back and make them compatible with a new filter.

Any suggestions? Thanks!

[amitchandak]

Have you checked out, some date slicer from marketplace that suits better.

[Anonymous]

@amitchandak Yes, and I haven't found anything that works for my particular issue.

[kentyler]

Are you using a calendar (or "date") table. If you had a calendar table in relationship to your date/time value you could filter on the fiscal year columns in the calendar table, and that would filter your date/time records. You might have to create a calculated column and extract only the date portion of your value to relate to the date table, as the date table will probably have a granularity of days.

You can find many offerings of prebuilt calendar tables, here's one https://www.sqlbi.com/tools/dax-date-template/

I'm a personal Power Bi Trainer I learn something every time I answer a question. I blog at http://powerbithehardparts.com/

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

[Anonymous]

@Anonymous Sorry, but I am not sure if I am following.

I am using a calendar table, and it does have a relationship to the timestamp value being used. Filtering on fiscal year(s) within this calendar table only reduces the amount of days/dates available in the relative date slicer. It does not address my issue of needing to make things filterable by fiscal month instead of fiscal day. 

As far as creating a calculated column to extract only the MM-YYYY portion of the timestamp, that seems promising. But I am battling issues. When I go to use a formula such as (format(date[date],"MM-YYYY"), dax won't even let me select the datestamp dimension within the date brackets. I.e. it is not a selectable option.

Thoughts?

[kentyler]

Can you post a small sample file... power bi can be a little different than excel in refering to values in columns

[Anonymous]

unfortunately, my corporation does not allow me to upload/share files.

all I can do is suggest solutions that I think will work, but I have struggled with...

for example, I have a field connected to the timestamp called 'monthnyear' that is basically in the format I need it. Issue is, it is not able to be leveraged in a relative filter.

It is not like I can create two simple dropdowns called "min monthnyear select" and "max monthnyear select" and have the data filter between the values. That would be ideal, but I'm not aware of a way to make that happen.