Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Backlog Balance by Week

New to DAX and could use some help.

 

I'm trying to create a simple view of the company's backlog balance by week.  So let's say we have the following set of columns: customer, contract sign date, install date, contract value.  And I want the x-axis to be a weekly date axis.  See the graph below.

 

If the customer has signed the contract but has not installed, they are considered in the backlog.  Once they are installed, they are removed from the backlog.

 

Imagine that you have weeks 3/1/19, 3/8/19, 3/15/19......8/9/19.  Customer Feel The Bern signs the contract on 3/4/19 and has an installation date of 8/6/19 with a contract value of $1,000,000.  Starting on reporting week 3/8, $1,000,000 would be included to the backlog.  And then come reporting week 8/9/19 the $1,000,000 would no longer be on the backlog balance.  See below for a simple data set and the corresponding graph that I would expect.

 

 

A caveat, I need to ability to drill in.  So on the chart, I need to be able to click on a bar for a specific week and see which customers comprise that balance.  

 

This is a very simple sumifs formula in excel but I'm very new to DAX and Power BI and the execution in Power BI is not obvious to me.  

 

First, I'm thinking I need a separate table that is essentially a set of date weeks (so a table of the dates you see in the graph above)? 

 

Guidance would be much appreciated.

 

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous ,

    You can try to use following measure formula if it suitable for your requirement.

    Measure =
VAR currDate =
    MAX ( calendar[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Contract Value] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Contract sign Date] <= currDate
                && [Install Date] > currDate
                && WEEKNUM ( [Contract Sign Date], 1 ) = WEEKNUM ( currDate, 1 )
        )
    )

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous ,

    You can try to use following measure formula if it suitable for your requirement.

    Measure =
VAR currDate =
    MAX ( calendar[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Contract Value] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Contract sign Date] <= currDate
                && [Install Date] > currDate
                && WEEKNUM ( [Contract Sign Date], 1 ) = WEEKNUM ( currDate, 1 )
        )
    )

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you.  I had to add some additional logic for the actual data, but this foundation was right on point.  I like the touch of creating the currDate variable as I had initially been repeating the same date function throughout the formula.

       

      For anyone that might read later looking for backlog balance wisdom, I added an additional calendar week table that was essentially just a laundry list of week end dates.  I created it in excel starting with 1/4/2019 (because it's the first friday of the year, and that's when my company ends our week), then used a (date + 7) formula in excel and dragged it down to the end of 2019 and just plopped it into Power BI and used that table as the axis.  When I ran the DAX formula similar to what was posted above, I actually didn't even need the WEEKNUM function, it worked without it.