Forum Discussion
Use calculate to filter data based on date column
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' ) )
What i want to achieve is using a measure to sum all sales that related to current week, last week, week minus 2, week minus 3.
volume current week = CALCULATE (
SUM ( 'Facts Combined_Agg'[Transaction Volume Amount] ),
'Actual Date'[Start Date of Week] = max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1)
I am able to work out the week range selection like below. But when i apply back to the above measure, I can't embed in it properly.
Current Week= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1
Last Week= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1-7
Week Minus 2= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1-14
Week Minus 3= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1-21
I upload the pbix file for play. https://1drv.ms/u/s!Aig3EWdV94jKgpsXaMC8AAp5TMW0UA?e=ROWk5D
There are two tables-date and Fact Combined_Agg. I build relationship between them,
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.