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, ...
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

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.