Forum Discussion
Anonymous
6 years agoNot applicable
Filter by date in bar chart
Hello all, I want to show four different bar charts for a volume column. 1. Volume by day; yesterday vs day before yesterday 2. Volume by week; Last Week to Date vs current WtD 3. Volume by m...
- 6 years ago
Hi Anonymous ,
I have created a sample for your reference.
day = var today = TODAY() var last = today-1 return CALCULATE(SUM('Table'[value]),FILTER('date','date'[Date]>=last && 'date'[Date]<=today))week_ = VAR today = TODAY () VAR weeknum = WEEKNUM ( today, 2 ) VAR weekday = WEEKDAY ( today, 2 ) VAR todayyearwekk = YEAR ( today ) * 100 + weeknum VAR lsw = CALCULATE ( MAX ( 'date'[yearweek_] ), FILTER ( ALL ( 'date' ), 'date'[yearweek_] < todayyearwekk ) ) RETURN CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'date', AND ( [yearweek_] = todayyearwekk || 'date'[yearweek_] = lsw, WEEKDAY ( [Date] ) <= weekday ) ) )YM = VAR today = TODAY () VAR st = DATE ( YEAR ( today ), MONTH ( today ), 1 ) VAR premonth = EDATE ( today, -1 ) VAR spre = DATE ( YEAR ( premonth ), MONTH ( premonth ), 1 ) RETURN CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'date', OR ( 'date'[Date] <= today && 'date'[Date] >= st, 'date'[Date] >= spre && 'date'[Date] <= premonth ) ) )Year = VAR today = TODAY () VAR stofyear = DATE ( YEAR ( today ), 1, 1 ) VAR spre = DATE ( YEAR ( today ) - 1, 1, 1 ) VAR pre = EDATE ( today, -12 ) RETURN CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'date', OR ( 'date'[Date] >= stofyear && 'date'[Date] <= today, 'date'[Date] >= spre && 'date'[Date] <= pre ) ) )For more details, please check the pbix as attached.
v-frfei-msft
Community Support
6 years agoHi Anonymous ,
I have created a sample for your reference.
day = var today = TODAY()
var last = today-1
return
CALCULATE(SUM('Table'[value]),FILTER('date','date'[Date]>=last && 'date'[Date]<=today))week_ =
VAR today =
TODAY ()
VAR weeknum =
WEEKNUM ( today, 2 )
VAR weekday =
WEEKDAY ( today, 2 )
VAR todayyearwekk =
YEAR ( today ) * 100 + weeknum
VAR lsw =
CALCULATE (
MAX ( 'date'[yearweek_] ),
FILTER ( ALL ( 'date' ), 'date'[yearweek_] < todayyearwekk )
)
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
'date',
AND (
[yearweek_] = todayyearwekk
|| 'date'[yearweek_] = lsw,
WEEKDAY ( [Date] ) <= weekday
)
)
)
YM =
VAR today =
TODAY ()
VAR st =
DATE ( YEAR ( today ), MONTH ( today ), 1 )
VAR premonth =
EDATE ( today, -1 )
VAR spre =
DATE ( YEAR ( premonth ), MONTH ( premonth ), 1 )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
'date',
OR (
'date'[Date] <= today
&& 'date'[Date] >= st,
'date'[Date] >= spre
&& 'date'[Date] <= premonth
)
)
)
Year =
VAR today =
TODAY ()
VAR stofyear =
DATE ( YEAR ( today ), 1, 1 )
VAR spre =
DATE ( YEAR ( today ) - 1, 1, 1 )
VAR pre =
EDATE ( today, -12 )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
'date',
OR (
'date'[Date] >= stofyear
&& 'date'[Date] <= today,
'date'[Date] >= spre
&& 'date'[Date] <= pre
)
)
)
For more details, please check the pbix as attached.
Anonymous
6 years agoNot applicable
Hello v-frfei-msft
Just tried your examples by using the codes and modifying them to my data, also looked in your attached PBIX file. I have to say WOW, this worked perfectly and exactly how I wanted it to do! Checked against my raw data and everything is exactly as i should!
Thank you very much for the help, appreciate it!