Forum Discussion
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 month; Last Month to Date vs current MtD
4. Volume by year; Last Year to Date vs current YtD
Right now I have the four charts but for week, month and year my view now is Last Full Week/Month/Year vs current volume to date. Meaning I see for example full last week vs current Week to Date (only two days so far), this makes the comparison quite strange. Is it possible to do something about this?
I use a calendar table linked to volume and use relative date filters and Offset filters for Week, Month and Year. As you can see below now the comparison becomes quite missrepresentative, especially for the month view.
Thanks!
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.
4 Replies
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.
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!
Anonymous
For week refer to my file: https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
For yesterday you can use
last day = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
For other prefer to use time intelligence with date calendar
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr)))) MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date]))) MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31"))) QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER))) Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER)))) Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER))) Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year)))) trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER)) trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER)) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Hello amitchandak
Thank you for the fast reply and information. However Im not sure this is the correct solution for me, as I tried to show in my post I need to use a stacked bar chart and show for example Last Week to "specific day" vs current week to "specific day". Say its tuesday today then I want my Week chart to show two bars, one for Last Week to Tuesday and one for Current Week to Tuesday. Is this possible?
For month It would then be Last Month to 14th of march vs Current Month to 14th of april. And this needs to be a relative filter because it will update everyday.
Thanks!
