Forum Discussion

naidoode's avatar
naidoode
Frequent Visitor
8 years ago

Dynamic date x-axis

Hey Guys

 

Im a PBI noob. And this is my first post...so be kind :smileyvery-happy:

Im not sure if the question was asked before...but, here goes.

I have a relative period slicer:

  • Last week
  • This month
  • Last month
  • This quarter
  • ...
  • This year

What I would like to achieve is this...

  • If Last Week is selected, the x-axis ahould display daily information (dates)
  • Same for this month and last month
  • If the user selects "This Quarter", then the x-axis should change to months
  • Same for year

So, is this do-able without using the date hierarchy? Im basically looking for a dynamic date hierarchy on the x-axis.

Looking at the images below. If I just use dates, it looks a mess (for this quarter), but then looks pretty if I use the Month hierarchy. I dont want the users to use the hierarchy though cos they will have to drill down again when moving to last week.

Fugly

 Pretty

 

 

 

 

 

All you help will be appreciated :smileywink:

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    naidoode Yes it is doable..

     

    Can you please share some sample data, So that I can easily give an update on this.

    • naidoode's avatar
      naidoode
      Frequent Visitor
      8 years ago

      Oooh...that was quick ;)

      Hmmmm....I dont have an option to attach a file...but it seems like I can paste. Would this be fine? I also have a date table (screenshot below)

       The reportdate field has a record per driver, per vehicle, per date for 1 year

      ReportDateDriverIDAccountIdVehicleIdDistanceMaxSpeedAvgSpeedDrivingDurationStopDurationNumOfStopsIdlingDurationGeoEvents
      2018-06-25301693028712342478215325398
      2018-06-253216939881195854597381752490
      2018-06-25611507777814674101127235149354167
      2018-06-25701507116191221320455166315826
      2018-06-2574150777101247325175828502129927138
      2018-06-25771507768801001991225119975
      2018-06-25771507770012610733137234356011644073
      2018-06-2578150714223000638634363832
      2018-06-25781507697900037109192371014
      2018-06-25801507763711465151206151540
      2018-06-25801507116221180371359534371352
      2018-06-258015071154001207171343271742
      2018-06-25801507115311132121140302121137
      2018-06-258015076987111122921513129246
      2018-06-25811507116152132336043042336022
      2018-06-2581150775631151216014011216033
      2018-06-2581150770302141370956536370925
      2018-06-25811507742411905215272224521515
      2018-06-2581150776040702152128121560
      2018-06-2584150769583786168318111126621541
      2018-06-2588150774580001275366311275117
      2018-06-258815077032156111222506144456916730108
      2018-06-25881507769911621592147102159226
      2018-06-25911507115171101192199354192129
      2018-06-2592150775630802403633124033
      2018-06-2592150769620005920059211
      2018-06-2592150771730002406024019
      2018-06-2592150710205016011921694111943
      2018-06-25941507715820186352050527205177854

      28

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        naidoode Try this in power query.

        I just took ReportDate and DrivingDuration columns from your data.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdLLDcQgDATQXnJOJM8Y86klSv9tLE602pj1hcPT4A/iPDcK+iH1oG37Vljadu2LWrERtUyFAIysk5VSotLDzfoShvNYCoujtlnpzeiTq/aIzbs1SNR74Hn+0J4tZlSjerOG98LlqQoCQX0FDsbrjqr13V+fVsSqcg8gI6o/gTEMW78VEuyK/q9gywqYVCRhG0x0MMmySPIXsvtpEhhJUkzmtNcH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ReportDate = _t, DrivingDuration = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"ReportDate", type date}, {"DrivingDuration", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "This Week", each if Date.IsInCurrentWeek([ReportDate]) then [ReportDate] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Last Week", each if Date.IsInPreviousWeek([ReportDate]) then [ReportDate] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "This Month", each if Date.IsInCurrentMonth([ReportDate]) then [ReportDate] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "This Qrtr", each if Date.IsInCurrentQuarter([ReportDate]) then [ReportDate] else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "This Year", each if Date.IsInCurrentYear([ReportDate]) then [ReportDate] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom4",{{"This Week", type text}, {"Last Week", type text}, {"This Month", type text}, {"This Qrtr", type text}, {"This Year", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"NA",Replacer.ReplaceValue,{"This Week", "Last Week", "This Month", "This Qrtr", "This Year"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"ReportDate", "DrivingDuration"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Type"}, {"Value", "Date"}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns","NA",null,Replacer.ReplaceValue,{"Date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type date}})
in
    #"Changed Type2"

         

        Make Type as slicer and put Date column in x axis of bar chart and DrivingDuration in values.