Forum Discussion

brazil's avatar
brazil
Regular Visitor
2 years ago

Comparing week with pervious week

Hi,

I have a measure as below which should return the sum of amount for the previous week, based on the User selecting the current week. I have my week index’s defined in my Date table. Not sure if I need the crossfilter bit at the end. When I use it I get identical results for this week and last week; when I leave it out I get no results for last week.

Almost feel like I need to be doing a ‘SQL self join’

 

 

Sum of Amount =

CALCULATE (

CALCULATE (

    SUM ( TransactionTable[Amount] ),

    FILTER ( TransactionType[Type] = "1" ),

    FILTER (

        ALL ( 'DateTable' ),

        'DateTable'[7DayCurrent]

            = SELECTEDVALUE 'DateTable'[7DayPrior])

    ),

    CROSSFILTER (' DateTable '[TransactDate], ' TransactionTable '[TDate], NONE )

) )

7 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    Here's a revised version of your code:

     

    Sum of Amount =
    CALCULATE (
    SUM ( TransactionTable[Amount] ),
    FILTER (
    ALL ( 'DateTable' ),
    'DateTable'[WeekIndex] = SELECTEDVALUE('DateTable'[WeekIndex]) - 1
    ),
    FILTER ( TransactionType, TransactionType[Type] = "1" ),
    CROSSFILTER ( 'DateTable'[TransactDate], 'TransactionTable'[TDate], NONE )
    )

     

    Explanation of changes:

    1. I replaced the nested CALCULATE with a single CALCULATE function.
    2. Removed the unnecessary inner CALCULATE and merged its filters with the outer CALCULATE.
    3. Used ALL('DateTable') to remove all filters on the 'DateTable', except for the one related to the previous week.
    4. Adjusted the filter condition to compare the 'WeekIndex' directly to the selected week's index minus 1.
    5. Moved the 'TransactionType' filter outside the 'DateTable' filter.

    Make sure your 'DateTable' has a 'WeekIndex' column or adjust the column name accordingly. Also, verify that your relationships between tables are set up correctly.

    This revised code should give you the sum of amounts for the previous week based on the user's selection of the current week. If you encounter any issues or if your data model differs significantly, please provide more details for further assistance.

     
     
     
    • brazil's avatar
      brazil
      Regular Visitor
      2 years ago

      thanks so much for your help. When i rewrite like you suggest I get the following as a result (table below):

      looks like the filter isn;t being applied to the prior week,

      Also - if you don't mind me asking - If I want to apply another filter to the DAX; can I just add it as another line like in the blue below?

      thansk again

       

       

       

       

       

      Sum of Amount =
      CALCULATE (
      SUM ( TransactionTable[Amount] ),
      FILTER (
      ALL ( 'DateTable' ),
      'DateTable'[WeekIndex] = SELECTEDVALUE('DateTable'[WeekIndex]) - 1
      ),
      FILTER ( TransactionType, TransactionType[Type] = "1" ),

      FILTER (CancelledTable, CancelledTable [CancelledFlag] <> TRUE,
      CROSSFILTER ( 'DateTable'[TransactDate], 'TransactionTable'[TDate], NONE )
      )

      • 123abc's avatar
        123abc
        Community Champion
        2 years ago

        It appears that you are trying to filter the data for the previous week using 'DateTable'[WeekIndex] = SELECTEDVALUE('DateTable'[WeekIndex]) - 1. However, this may not be sufficient if your week indices are not continuous or if there are gaps in the data.

        To ensure you are filtering for the correct previous week, you can use the DATESBETWEEN function:

         

        Sum of Amount =
        CALCULATE (
        SUM ( TransactionTable[Amount] ),
        FILTER (
        ALL ( 'DateTable' ),
        'DateTable'[Date] >= MIN('DateTable'[7DayPrior]) &&
        'DateTable'[Date] < MAX('DateTable'[7DayCurrent])
        ),
        FILTER ( TransactionType, TransactionType[Type] = "1" ),
        FILTER (CancelledTable, CancelledTable[CancelledFlag] <> TRUE)
        )

         

        In this formula, MIN('DateTable'[7DayPrior]) and MAX('DateTable'[7DayCurrent]) are used to determine the range for the previous week based on your 'DateTable' structure. Adjust these based on your specific date columns and week definitions.

        Regarding your question about adding another filter, yes, you can add more filters by adding additional FILTER functions. For example:

         

        Sum of Amount =
        CALCULATE (
        SUM ( TransactionTable[Amount] ),
        FILTER (
        ALL ( 'DateTable' ),
        'DateTable'[Date] >= MIN('DateTable'[7DayPrior]) &&
        'DateTable'[Date] < MAX('DateTable'[7DayCurrent])
        ),
        FILTER ( TransactionType, TransactionType[Type] = "1" ),
        FILTER (CancelledTable, CancelledTable[CancelledFlag] <> TRUE),
        FILTER ( AnotherTable, AnotherTable[AnotherColumn] = "SomeValue" )
        )

         

        This adds another filter condition based on a hypothetical 'AnotherTable' and its column 'AnotherColumn'.

        Make sure the syntax and column references match your data model for the added filter conditions. If you encounter any issues or have more details to share, feel free to provide additional information.

         

         

  • brazil's avatar
    brazil
    Regular Visitor
    2 years ago

    thanks so much for your help, UNfortunatly neither approach is working for me.

    Using the Datesbetween I get blank results and when I use the reworked version with Crossjoin I get identical results for both weeks:  really feels like what you propose should work - thanks again