locked
DAX Challenge - Count of Weeks Inactive RRS feed

  • Question

  • Hi guys

    I have two tables in my model:

    1. fctProjects which contains a list of employees, their projects and weeks (and about 30+ other columns) with YTD Revenue.
    2. a dimDate table.

    The model works as intended currently, but now I've been asked to add a calculated column for the Count of Weeks Inactive, i.e. how many weeks have lapsed since a projects YTD value last changed. Unfortunately, due to the nature of the data (YTD) this is difficult to do.  

    I have one visible measure in my model, [Net Revenue].  I have nested it as follows:


    Net Revenue YTD:=CALCULATE (
        SUM( [Value] ),
        FILTER( 'fctProjects', 'fctProjects'[Attribute] = "YTD Net Revenue" ),
        FILTER ( dimDate, dimDate[YYYYPPWW] = MAX ( dimDate[YYYYPPWW] ) )
    )


    Net Revenue YTD (Prior Week):=CALCULATE (
        [Net Revenue YTD],
        FILTER ( ALL ( dimDate ), dimDate[Weekly Index] = MAX ( dimDate[Weekly Index] ) - 1 )
    )


    Net Revenue YTD (Prior Fiscal Period):=CALCULATE (
        [Net Revenue YTD],
        FILTER ( ALL ( dimDate ), dimDate[Fiscal PeriodIndex] = MAX ( dimDate[Fiscal PeriodIndex] ) - 1 )
    )


    Net Revenue:=SWITCH (
        TRUE(),
        ISFILTERED ( dimDate[YYYYPPWW] ) || ISFILTERED ( dimDate[Week] ) || ISFILTERED ( dimDate[Week Name] ), [Net Revenue YTD] - [Net Revenue YTD (Prior Week)],
        ISFILTERED ( dimDate[Fiscal Period] ) || ISFILTERED ( dimDate[Fiscal Period Name] ) || ISFILTERED ( dimDate[YYYYPP] ), [Net Revenue YTD] - [Net Revenue YTD (Prior Fiscal Period)],
    [Net Revenue YTD]
    )


    Any help would be appreciated!  I'm on struggle street with this one.

    Cheers,
    Simon

    • Edited by Simon Nuss Friday, December 2, 2016 1:49 PM
    Friday, December 2, 2016 1:42 PM

Answers

  • Hi Simon There are some best practices in Power Pivot data modelling that you should consider. Often if these are not implemented, everything else gets harder. 30+ columns in your data table is a lot, and is not ideal. Consider removing what you can live without (no impact on this problem however). Do you have a net revenue attribute that is not YTD? If so, I would get rid of the YTD attribute all together and write a measure to calculate it on the fly using TOTALYTD or a custom formula. It is unlikely that you should write a calculated column for your new need. The exact formula will depend on your data, but I assume you can write a measure that checks for the last date, and works out the weeks from there. If you can load a sample workbook, I would be happy to take a look.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, December 2, 2016 9:18 PM
    Answerer