Forum Discussion

majidht's avatar
majidht
Frequent Visitor
9 years ago
Solved

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 yo...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi majidht,

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



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



    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.



    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

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

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

  • majidht's avatar
    majidht
    Frequent Visitor
    9 years ago

    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?

    • Tulio_DL's avatar
      Tulio_DL
      Helper I
      9 years ago

      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.

      • majidht's avatar
        majidht
        Frequent Visitor
        9 years ago

        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?

    • majidht's avatar
      majidht
      Frequent Visitor
      9 years ago

      vanessafvg

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

       

      Thank you,

      • Tulio_DL's avatar
        Tulio_DL
        Helper I
        9 years ago

        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 =]