Forum Discussion
Use calculate to filter data based on date column
- 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' ) )
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 days
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 - 7, --Substract 7 days for previous
REMOVEFILTERS( 'Actual Date' )
)I don't know selectvalue cam be use like this in calculate dax.
- Let me try it tomorrow. Thanks mate.