Forum Discussion

unknown917's avatar
unknown917
Helper IV
1 year ago
Solved

Count based on variable date range and start date

I have a table with a column that supplies a start date for a reopening of any given ID.  In another table I have the current and historical operating data for the ID by date with key items like sale...
  • techies's avatar
    techies
    1 year ago

    Hi unknown917 please try this

     

    create a calculated column in your sales table for the week buckets 

     

    Week_Bucket =
    VAR ReOpenDate = LOOKUPVALUE('reopen'[Reopen Date], 'reopen'[ID], 'sales'[ID])
    VAR DaysSince = DATEDIFF(ReOpenDate, 'sales'[Date], DAY)
    RETURN
    SWITCH(
        TRUE(),
        DaysSince >= 0 && DaysSince < 28, "Week 1-4 After",
        DaysSince >= 28 && DaysSince < 56, "Week 5-8 After",
        DaysSince >= 56 && DaysSince < 84, "Week 9-12 After",
        DaysSince < 0 && DaysSince >= -28, "Week 1-4 Before",
        DaysSince < -28 && DaysSince >= -56, "Week 5-8 Before",
        DaysSince < -56 && DaysSince >= -84, "Week 9-12 Before",
        "Other"
    )

     

    And then create the measure to sum the sales based on the buckets

     

    Sales_by_Weeks =
    VAR SelectedWeek = SELECTEDVALUE('SalesData'[Week_Bucket])
    RETURN
    CALCULATE(SUM('SalesData'[Sales Qty]), 'SalesData'[Week_Bucket] = SelectedWeek)

techies's avatar
techies
Super User
1 year ago

 Hi unknown917 please share the sample pbix file if possible.

  • unknown917's avatar
    unknown917
    Helper IV
    1 year ago

    My apologies, techies  - I am unable to share a file, but here is a sample of the tables I'm referring to:

     

    table 1
    IDReOpenDate
    11/6/2025
    22/17/2025
    31/13/2025
    411/4/2024
    53/10/2025
    table 2
    IDInv DateSales Qty
    19/9/2024150
    19/12/2024175
    19/16/2024205
    19/19/2024234
    211/13/202417
    21/15/202512
    32/13/2025100
    32/20/2025125
    33/11/2025123
    43/12/20255
    510/22/2024500
    510/24/2024400

     

    Intended result is to select the ID with a slicer and see performance after reopen:

    IDwk 1-4 (28days)wk 5-8 (28days)wk 9-12 (28 days)
    1   
    2   
    30225123
    4   
    5   

     

    Metrics before re-open date could also be valuable, not sure if that is also possible

    • techies's avatar
      techies
      Super User
      1 year ago

      Hi unknown917 please try this

       

      create a calculated column in your sales table for the week buckets 

       

      Week_Bucket =
      VAR ReOpenDate = LOOKUPVALUE('reopen'[Reopen Date], 'reopen'[ID], 'sales'[ID])
      VAR DaysSince = DATEDIFF(ReOpenDate, 'sales'[Date], DAY)
      RETURN
      SWITCH(
          TRUE(),
          DaysSince >= 0 && DaysSince < 28, "Week 1-4 After",
          DaysSince >= 28 && DaysSince < 56, "Week 5-8 After",
          DaysSince >= 56 && DaysSince < 84, "Week 9-12 After",
          DaysSince < 0 && DaysSince >= -28, "Week 1-4 Before",
          DaysSince < -28 && DaysSince >= -56, "Week 5-8 Before",
          DaysSince < -56 && DaysSince >= -84, "Week 9-12 Before",
          "Other"
      )

       

      And then create the measure to sum the sales based on the buckets

       

      Sales_by_Weeks =
      VAR SelectedWeek = SELECTEDVALUE('SalesData'[Week_Bucket])
      RETURN
      CALCULATE(SUM('SalesData'[Sales Qty]), 'SalesData'[Week_Bucket] = SelectedWeek)

      • unknown917's avatar
        unknown917
        Helper IV
        1 year ago

        Genius techies !  I can make this work!  Thank you for your help!