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 ...
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
Icon for Community Champion rankCommunity 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.