Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating Measures in Context to Custom 445 Calendar

I am trying to show "Line & Stacked Column Chart" (As seen in the screenshot) to show: what is to be shipped current period (Red color stack bar), what is to be shipped next period (Gray color stack bar) and what is to be shipped in future periods (Blue color stack bar). The chart is built using data from two tables: Dates Table (dimension) and Sales Orders table (Fact).

Our company converted to custom 445 calendar this year with calendar beginning on for instance, 1/1/2021 and ending on for instance, 12/31/2021 but with the following Caviat: Following days are allocated for each period (Month):
01 = 23
02 = 28
03 = 35
04 = 28
05 = 28
06 = 35
07 = 28
08 = 28
09 = 35
10 = 28
11 = 28
12 = 41
Hence, our month start and ends are different from a standard calendar (i.e. Period 1 Starts on 1/1/21 and ends on 1/23/21; Period 2 starts on 1/24/21 and ends on 2/20/21; Period 3 stars on 2/21/21 and ends on 3/27/21; and so on based on the days allocated for each period as I explained in the couple of sentences back. We have been using standard calendar prior to this year (i.e. the year 2020, 2019, 2018, etc.). I do have a Period Start and Period End columns in the date table indicating the start and the end dates for not only the 445 date sequences I explained in the couple of sentences back, but also start and end date for when we were using standard calendar in the prior years (i.e. January start was 1/1 and end on 1/31; March start was 3/1 and end on 3/31; and so on). Hence, these two columns (Period start and Period end) along with a column called "Period" (indicating the month number) are the keys to creating our measures. 

I have an active relationship between Booking Date (Sales Orders table) and Date (Dates table). I have an inactive relationship between Planned Shipped Date (Sales Orders table) and Date (Dates table). The order booked value is stored in its own column within the Sales Orders table. 

 

I am looking create a measure that calculates what is to be shipped current period and to be shipped next period based on the booking value for a particular date which will be shown in the chart. Further, I would like the measure to be irrelevant of any particular date, timeframe, etc. In another words, I want the measure to auto-evaluate based on the filters to be placed on this visual (i.e. I want the visual to contain and show this data for last 45 days including today). 

 

  • Anonymous's avatar
    Anonymous
    5 years ago

    *UPDATE*

     

    I came up with an alternate solution in the meantime. 

     

    I have a calculated column in the date table labeled "Date Context". Below is the measure for it:

     

    Date Context =
    SWITCH(
    TRUE(),
    Dates[Period] < MONTH(EDATE(TODAY(),-1)) && Dates[Year] <= YEAR(EDATE(TODAY(),-1)), "PriorPeriods",
    Dates[Period] = MONTH(EDATE(TODAY(),-1)) && Dates[Year] = YEAR(EDATE(TODAY(),-1)), "PreviousPeriod",
    Dates[Period] = month(TODAY()) && Dates[Year] = YEAR(TODAY()), "CurrentPeriod",
    Dates[Period] = MONTH(EDATE(TODAY(),1)) && Dates[Year] = YEAR(EDATE(TODAY(),1)), "NextPeriod",
    Dates[Period] = MONTH(EDATE(TODAY(),2)) && Dates[Year] = YEAR(EDATE(TODAY(),2)), "FollowingPeriod",
    Dates[Period] > MONTH(EDATE(TODAY(),2)) &&Dates[Year] >= YEAR(EDATE(TODAY(),3)),"FuturePeriods",
    "FuturePeriods")
     
    Further, above measure ties in with PeriodStart and PeriodEnd columns and evaluates conditions. 
     
    I then leveraged above column and created the following measure:
     
    1. Next Period:
    VAR PPBegin =
    LOOKUPVALUE(
    'Dates'[PeriodStart],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "PreviousPeriod"
    )
    )

    VAR CPBegin =
    LOOKUPVALUE(
    'Dates'[PeriodStart],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "CurrentPeriod"
    )
    )

    VAR CPEnd =
    LOOKUPVALUE(
    'Dates'[PeriodEnd],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "CurrentPeriod"
    )
    )

    VAR NPBegin =
    LOOKUPVALUE(
    'Dates'[PeriodStart],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "NextPeriod"
    )
    )

    VAR NPEnd =
    LOOKUPVALUE(
    'Dates'[PeriodEnd],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "NextPeriod"
    )
    )

    RETURN
    Sumx(FILTER(factSalesOrders,<Filter(s) of your choice based on your organization> &&
    factSalesOrders[Ship Date] >= IF(factSalesOrders[Booked Date] < CPBegin && factSalesOrders[Booked Date] >= PPBegin, CPBegin, NPBegin) &&
    factSalesOrders[Ship Date] <= IF(factSalesOrders[Booked Date] < CPBegin && factSalesOrders[Booked Date] >= PPBegin, CPEnd, NPEnd)
    ),
    factSalesOrders[Order Booked])
     
    In another words, in using specific variable from that column, i can offset dates to start with a specific date populated within the PeriodStart column and end the count by a specific date populated within the PeriodEnd column. 
     
    2. Current Period:
    Sumx(FILTER(factSalesOrders,<Filter(s) of your choice based on your organization> &&
    factSalesOrders[Ship Date] >= RELATED(Dates[PeriodStart]) &&
    factSalesOrders[Ship Date] <= RELATED(Dates[PeriodEnd])
    ),
    factSalesOrders[Order Booked])
     
    3. Future Period:
     Total Merch Booked - (Current Period + Next Period)
     
         

4 Replies

  • Anonymous , You can create rank of period start date or year period YYYYPP

     

    new column in date table

    Period Rank = RANKX(all('Period'),'Period'[period start Date],,ASC,Dense)

    or

    Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

     

     new measure
    This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
    Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

    Next Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])+1))

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak ,

      Thank you for replying back. Both PeriodRank measure above produce "1" and the measure did not work. 

      Below is a screen capture of the date table. My main focus is to use the first three columns (Period, PeriodStart, and PeriodEnd) confinded in the screenshot below by the blue line (Far left side of the screenshot) along with perhaps may be using the new calculated column i created called "PeriodRank" (Far right side in the screenshot), which represents sequential number for each period starting on the beginning of the time, January 1, 2010 which is the start date for the calendar (i.e. Period 1 of 2021 is represented with rank of 133; Period 2 of 2021 is represented with rank of 134; and so on) 

      Further, Below is a screenshot of columns in the Sales order table(fact) which we have to use in order create these measures. As i talked about, i have a shipdate, booked date, and order booked (this contains the value).

      Furthermore, I had tried to create the following measures:

       

      1.

      Ship Current Total =
      var _shiprank = (CALCULATE(
      SELECTEDVALUE( Dates[PeriodRank]),
      TREATAS(values(factSalesOrders[Ship Date]), Dates[Date])
      ))
      var _bookedrank = (CALCULATE(
      SELECTEDVALUE( Dates[PeriodRank]),
      CROSSFILTER(
      Dates[Date],
      factSalesOrders[Booked Date],
      Both
      )
      ))
      RETURN
      CALCULATE(SUM(factSalesOrders[Order Booked]),FILTER(factSalesOrders, factSalesOrders[DocumentType] = "O" &&
      _shiprank-_bookedrank = 0), factSalesOrders[Order Booked])
       
      2. 
      Ship Next Total =
      var _shiprank = (CALCULATE(
      SELECTEDVALUE( Dates[PeriodRank]),
      TREATAS(values(factSalesOrders[Ship Date]), Dates[Date])
      ))
      var _bookedrank = (CALCULATE(
      SELECTEDVALUE( Dates[PeriodRank]),
      CROSSFILTER(
      Dates[Date],
      factSalesOrders[Booked Date],
      Both
      )
      ))
      RETURN
      CALCULATE(SUM(factSalesOrders[Order Booked]),FILTER(factSalesOrders, factSalesOrders[DocumentType] = "O" &&
      _shiprank-_bookedrank = 1), factSalesOrders[Order Booked])
       
      3. 
      Ship Future Total =
      var _shiprank = (CALCULATE(
      SELECTEDVALUE( Dates[PeriodRank]),
      TREATAS(values(factSalesOrders[Ship Date]), Dates[Date])
      ))
      var _bookedrank = (CALCULATE(
      SELECTEDVALUE( Dates[PeriodRank]),
      CROSSFILTER(
      Dates[Date],
      factSalesOrders[Booked Date],
      Both
      )
      ))
      RETURN
      CALCULATE(SUM(factSalesOrders[Order Booked]),FILTER(factSalesOrders, factSalesOrders[DocumentType] = "O" &&
      _shiprank-_bookedrank > 1), factSalesOrders[Order Booked])
       
      Continues in the next reply....
      • Anonymous's avatar
        Anonymous
        Not applicable

        I am able to create following visual table in Power Bi using all three measures mentioned above. Further, it gives me a correct breakdown of what is shipping current, next and future periods.

        However, my problem is that I am not able to show "Line and Stacked Column Chart" correctly as I am intending to show as I have posted the screenshot of the chart in the beginning of this post. In another words, although all three measures are working when I bring in the Ship Dates column, they all needs to tie in with Booked Date which has an active relationship with the date in dates table. 

         

  • Anonymous's avatar
    Anonymous
    Not applicable

    *UPDATE*

     

    I came up with an alternate solution in the meantime. 

     

    I have a calculated column in the date table labeled "Date Context". Below is the measure for it:

     

    Date Context =
    SWITCH(
    TRUE(),
    Dates[Period] < MONTH(EDATE(TODAY(),-1)) && Dates[Year] <= YEAR(EDATE(TODAY(),-1)), "PriorPeriods",
    Dates[Period] = MONTH(EDATE(TODAY(),-1)) && Dates[Year] = YEAR(EDATE(TODAY(),-1)), "PreviousPeriod",
    Dates[Period] = month(TODAY()) && Dates[Year] = YEAR(TODAY()), "CurrentPeriod",
    Dates[Period] = MONTH(EDATE(TODAY(),1)) && Dates[Year] = YEAR(EDATE(TODAY(),1)), "NextPeriod",
    Dates[Period] = MONTH(EDATE(TODAY(),2)) && Dates[Year] = YEAR(EDATE(TODAY(),2)), "FollowingPeriod",
    Dates[Period] > MONTH(EDATE(TODAY(),2)) &&Dates[Year] >= YEAR(EDATE(TODAY(),3)),"FuturePeriods",
    "FuturePeriods")
     
    Further, above measure ties in with PeriodStart and PeriodEnd columns and evaluates conditions. 
     
    I then leveraged above column and created the following measure:
     
    1. Next Period:
    VAR PPBegin =
    LOOKUPVALUE(
    'Dates'[PeriodStart],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "PreviousPeriod"
    )
    )

    VAR CPBegin =
    LOOKUPVALUE(
    'Dates'[PeriodStart],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "CurrentPeriod"
    )
    )

    VAR CPEnd =
    LOOKUPVALUE(
    'Dates'[PeriodEnd],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "CurrentPeriod"
    )
    )

    VAR NPBegin =
    LOOKUPVALUE(
    'Dates'[PeriodStart],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "NextPeriod"
    )
    )

    VAR NPEnd =
    LOOKUPVALUE(
    'Dates'[PeriodEnd],
    'Dates'[Date Context], LOOKUPVALUE(
    Dates[Date Context],
    Dates[Date Context], "NextPeriod"
    )
    )

    RETURN
    Sumx(FILTER(factSalesOrders,<Filter(s) of your choice based on your organization> &&
    factSalesOrders[Ship Date] >= IF(factSalesOrders[Booked Date] < CPBegin && factSalesOrders[Booked Date] >= PPBegin, CPBegin, NPBegin) &&
    factSalesOrders[Ship Date] <= IF(factSalesOrders[Booked Date] < CPBegin && factSalesOrders[Booked Date] >= PPBegin, CPEnd, NPEnd)
    ),
    factSalesOrders[Order Booked])
     
    In another words, in using specific variable from that column, i can offset dates to start with a specific date populated within the PeriodStart column and end the count by a specific date populated within the PeriodEnd column. 
     
    2. Current Period:
    Sumx(FILTER(factSalesOrders,<Filter(s) of your choice based on your organization> &&
    factSalesOrders[Ship Date] >= RELATED(Dates[PeriodStart]) &&
    factSalesOrders[Ship Date] <= RELATED(Dates[PeriodEnd])
    ),
    factSalesOrders[Order Booked])
     
    3. Future Period:
     Total Merch Booked - (Current Period + Next Period)