Forum Discussion
Using a Relative Time Stamp (MM/DD/YYYY HH:MM:SS) filter to set start and end fiscal month?
Solution SageAre 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
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.