cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
majidht
Frequent Visitor

Slicer - Date and Time

Hello,

I've created a cube using visual studio and I am trying to get reports with Power BI. I need to have specific times frames for my report. The only options I saw on Power BI are slicers. Do you know how to have a specific time frame? (I prefer using connect live option but if the answer requires me to use import option and create columns that's fine too).

 

Thank you,

Majid

1 ACCEPTED SOLUTION

Hi @majidht,

I can reproduce your scenario, I get date from SSAS with inport mode as follows.

1.PNG

First, I can change the Text to Date type using the Formatting feature, please see the following screenshot.

2.PNG

Second, If you can not change the data type directly, you can split the date column to year, month, day column in Power Query Editor. Please select Split Column under on Query Editor Home, click appropriate delimiter.

3.png

When you get year, mon, day. If Month column is January format, you can use the following formula to change it to number.

SWITCH([Month],  "January", 1,"February", 2,  "March",3,  "April",4
               , "May", 5,"June", 6, "July",  7, "August",8
               , "September",9, "October",  10,  "November",11, "December" 12
               , 0 ) 


Then you can use DATE function to create calculated column to get Date column.

Date=DATE([Year],[Month],[day])


Finally, you can create a slicer as @Tulio_DL posted. In addition, there is a similar thread you can reference to.

Best Regards,
Angelia

View solution in original post

14 REPLIES 14
vanessafvg
Super User
Super User

@majidhtdefine specific time frame you can either filter out the data at the power query level (in imported mode)

or you can use the slicers to define date

or you can hard code some date specifics in your visual / page / report filters





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks for your reply. Imagine I want to see the sales between March 1st 2016 to Feb 15th 2017. How would you do it using slicers?

@vanessafvg

Like I want a bar chart for sales by category but in a specific time fram.

 

Thank you,

@majidht

 

 

I don't know if i quite understood what you're trying to do. In my mind you have a whole report of sales working perfectly, but at just 1 of the graphs you would like it filtered by a period of time, is that so?

 

If so, by sellecting the graph and you can add the 'Calendar'[dates] as a filter on the visualization pannel. That will let you filter that just 1 graph by dates.

 

If it's quite not there yet, please give me more details and I'll try to help!

 

Hope it helps =]

@Tulio_DL

Let me give you an example. I created facts and dimensions in SQL analysis server. In my current report I can create different charts and tables and slicers. For example, I want to see sales count by product category or sales by customer type. I can do this and then have a slicer for year, quarter or month to give me sales number by product category only in 2017 or only in January 2016. But imagine I want the same graph(sales number by product category) only in a specific time fram(like from 02/04/2015 to 03/05/2016). In this scenario, I cannot use a slicer or a filter to specify the start date and end date. Now I have to questions:

1-Is this achievable when I use connect live option(I connect to analysis server)?

2- If not, what is the solution if I use import option(to my understanding I need columns and DAX queries to make this possible)?

@majidht

 

Aren't you looking for a slicer like this?

Sem título.jpg

 

 

@Tulio_DL

Yes exactly!

Ok!!

 

Don't worry @majidht, that's quite simple to achieve and is the way I mentioned up on this topic:

 

1. You need to add a filter to your reportSem título10.jpg

 

 2. Drag your date column into it, make sure your data is modeled into DATE/TIME

 

You said you're connecting to a SQL database to reach your data, right? Something that happened to me when I started using PowerBI was that I used to connect to SQL by DirectQuery because it was so much faster.... But it turns out that I wasn't able to modelate data the way I wanted, so I changed the way to connect from Direct Query to Import Data so I could set up columns the way I pleased.

@Tulio_DL

 

Yes! that's the problem. I am getting data from SSAS(I've created cubes and dimensions in visual studio), so there reason I am having problems is that all my dimensions are in text data type and I cannot convert them to numeric or date. I think my original question has been totally changed lol. 

@majidht if you are using ssas and you have a time dimension  you need to make sure your settings are correct on the ssas side

 

in your time dimension make sure you have your date field in the value columm ( you get value, key and name), the value must be set to the date field





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks @Tulio_DL @vanessafvg @v-huizhn-msft for your replies it was great help to me guys.

Hi @majidht,

I can reproduce your scenario, I get date from SSAS with inport mode as follows.

1.PNG

First, I can change the Text to Date type using the Formatting feature, please see the following screenshot.

2.PNG

Second, If you can not change the data type directly, you can split the date column to year, month, day column in Power Query Editor. Please select Split Column under on Query Editor Home, click appropriate delimiter.

3.png

When you get year, mon, day. If Month column is January format, you can use the following formula to change it to number.

SWITCH([Month],  "January", 1,"February", 2,  "March",3,  "April",4
               , "May", 5,"June", 6, "July",  7, "August",8
               , "September",9, "October",  10,  "November",11, "December" 12
               , 0 ) 


Then you can use DATE function to create calculated column to get Date column.

Date=DATE([Year],[Month],[day])


Finally, you can create a slicer as @Tulio_DL posted. In addition, there is a similar thread you can reference to.

Best Regards,
Angelia

Hello majidht!

 

 

You can do it by creating a CALENDAR table and establishing a relationship between your 'Sales'[dates] to 'Calendar'[dates].

 

Then on your report you create a filter with your 'Calendar'[dates].

 

Anda that should do the work!

 

Hope that helps.

@Tulio_DL

I have a Date table and sales tables with proper relationships. Currently, I can create reports and have year, quarter and month slicers but my question is what if the client wants to create a report like number of sales from March 1st 2015 to Jan 20th 2017? In that case slicers would not help. Do you know how to solve this?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.