Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

dynamic date range based on slicer selection

I have a data mart that contains content for time worked on jobs.  My fact table holds the times worked.  Its grain is employee + job + date.  Its data spans all of history (i.e., many years).  I have 3 dimension tables - employee,  job, and date.  My company uses a 4-5-4 52/53 fiscal calendar.  So, my period start and end dates change, and they don't usually correspond to a regular calendar month's start and end dates.  My date table holds this fiscal calendar information (e.g., Period Number, Period Start, Period End).

 

I need to create a bar chart of the sum of time worked per period, for the most recent year.  The time range will be the 3 periods (from the most recent year) according to a period selection by a user in a slicer.  For example, if the user selects period 2 in the slicer, the bar chart needs to show periods 2, 3, and 4 (from the most recent year); if a user selects period 7, the bar chart needs to show periods 7, 8, and 9 (again, from the most recent year).

 

How can I achieve this?

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      The solution in that link doesn't solve my problem.  I don't need to dynamically toggle dimension attributes.  I need to dynamically return a different date range (but always the same length of time, i.e., 3 months) based upon a selection in a slicer.  As far as I know, that is a different "dynamic axis" problem and different solution technique.  I found a very similar question (and solution) below:

      https://community.powerbi.com/t5/Desktop/Show-last-13-months-based-on-user-single-slicer-selection/td-p/64214

       

      However, my solution is still not working.

       

      Here is my data model:

       

      [Lbr Wk Key] joins my fact table to my Dimension Lbr Wk table.  [Lbr Wk Key] is pretty much a date (really, week) numerical ID, which has format YYYMMDD (e.g., 20200215).  On table Dimension Lbr Wk, there are 3 columns of significance.  They are [FY Pd ID], [FY Pd], and [Next 3 Periods].  [FY Pd ID] is a numerical ID for the fiscal year and period, with values like '20062007.01' (i.e., the first period of FY 06-07) and '20192020.12' (i.e., the last period of FY 19-20).  [FY Pd] is the "display label" for fiscal year and period, with values like 'FY06-07 Pd01 (Apr)' and 'FY19-20 Pd12 (Mar)'.  [Next 3 Periods] identifies the period 3 months from the period in [FY Pd ID] (with the same format).  So, if [FY Pd ID] is '20062007.01', then [Next 3 Periods] is '20062007.03'.

       

      I want a clustered column chart that shows some measure (say, Sum(Time)) for a time range of 3 periods, where the first period is selected in a slicer.  Both my slicer and my column chart display [FY Pd].  My solution (which isn't working, but is based on the solution in the link that I posted) is to return a blank measure "value" for periods that do not fall in the time range given by the period selection in the slicer, and to suppress blank measure "values" in the axis field properties.  Here is my measure:

       

      Sum of Time = 
var BeginDate = MAX('Dimension Lbr Wk'[FY Pd ID])
var EndDate = MAX('Dimension Lbr Wk'[Next 3 Periods])

var SumOfTime = Sum('Fact Fcst and Actuals'[Time])

return

if(
    MIN('Dimension Lbr Wk'[FY Pd ID]) < BeginDate
    ,blank()
    ,if(
        MIN('Dimension Lbr Wk'[FY Pd ID]) > EndDate
        ,blank()
        ,SumOfTime
    )
)

       

       

      Here is what my visuals are currently showing:

       

      Here is confirmation that 'Show items with no data' is not checked on the axis of the column chart:

       

      What am I doing wrong in the measure definition?  I suspect it has something to do with the min/max functions, but I don't know how to resolve.