Comparing week with pervious week

[brazil]

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 )

) )

[brazil]

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

[123abc]

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]

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]

I see you've encountered some issues with the filter not being applied to the prior week. Let's address this concern and also discuss how to add another filter to your DAX expression.

Addressing Filter Issue:

It appears that you are trying to filter the data based on the 'DateTable'[WeekIndex] for the prior week. Ensure the following:

1. Verify WeekIndex Field Type:

   • Make sure that 'DateTable'[WeekIndex] is of the correct data type (possibly an integer) and represents weeks appropriately.

2. Check DateTable Relationship:

   • Confirm that there is a proper relationship between 'DateTable' and 'TransactionTable' based on the date fields.

3. Ensure WeekIndex Exists:

   • Double-check that there is data for the prior week in your 'DateTable' with the corresponding 'WeekIndex.'

Adding Another Filter:

To add another filter, you can indeed extend your DAX expression as you've attempted in the blue section. However, it seems like there might be a mistake with the placement of the CROSSFILTER function.

Here's a modified version:

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 )
)

Make sure that the CROSSFILTER is outside the FILTER block for 'CancelledTable' to ensure it applies to the entire CALCULATE context.

If the issues persist, consider examining your data, relationships, and data types more closely. Additionally, you can use tools like DAX Studio to evaluate and debug your DAX expressions.

[brazil]

thanks so much for your input. My index is definatly a continious list with no gaps.

I will try the alternative you suggest below.

thanks again

[123abc]

You're welcome! If your week index is a continuous list without gaps, and you have correctly defined relationships between your tables, the modified DAX expression I provided should work for filtering the sum of amounts for the previous week.

Feel free to give it a try, and if you encounter any further issues or have additional questions, don't hesitate to ask. Good luck with your Power BI project!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc]

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.