Forum Discussion
gavin007
4 years agoHelper V
Use calculate to filter data based on date column
Hi all, it sounds a simple task but I must have done something wrong. I have a simple fact table with transaction volume and batch date column as date type. When I use the following dax, the result i...
- 4 years ago
gavin007
In your pbix, the way you calculate the Current week gives Week Start date = 2021/11/29.
It seems to me that the current week should start on 2021/11/22 based on your calendar table?
The table Fact COmbined Agg doesn't have any data for 2021. Its kinda difficult for us to help you.
You can use a variable to find what is the current week based on Today's date, then calculate the Volume for this week.Volume_Current = VAR _CurrentWeek = CALCULATE ( SELECTEDVALUE ( 'Actual Date'[Start Date of Week] ), REMOVEFILTERS ( 'Actual Date' ), 'Actual Date'[Date] = TODAY () ) RETURN CALCULATE( SUM( 'Facts Combined_Agg'[Transaction Volume Amount] ), 'Actual Date'[Start Date of Week] = _CurrentWeek, REMOVEFILTERS( 'Actual Date' ) )
If you want to week before the Current one, you can substract 7 daysVolume_Current = VAR _CurrentWeek = CALCULATE ( SELECTEDVALUE ( 'Actual Date'[Start Date of Week] ), REMOVEFILTERS ( 'Actual Date' ), 'Actual Date'[Date] = TODAY () ) RETURN CALCULATE( SUM( 'Facts Combined_Agg'[Transaction Volume Amount] ), 'Actual Date'[Start Date of Week] = _CurrentWeek - 7, --Substract 7 days for previous REMOVEFILTERS( 'Actual Date' ) )