Forum Discussion
Using a Relative Time Stamp (MM/DD/YYYY HH:MM:SS) filter to set start and end fiscal month?
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!
6 Replies
- kentyler
Solution Sage
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
- 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
- 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
- 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.
- 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 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
- amitchandak
Super User
Have you checked out, some date slicer from marketplace that suits better.
amitchandak Yes, and I haven't found anything that works for my particular issue.
