Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!