Forum Discussion

NewbieJono's avatar
NewbieJono
Post Partisan
4 years ago

Total Weekly Figure in trend graph

hello, i have a date table linked to a Fact table.

 

is there a method of showing the total weekly volume Mon-Sun. and trend over the past 3 weeks. e,g a graph with 4 bars showing the total each week.

 

at the moment all the volumes are recoreded daily 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    NewbieJono , Last 4 week

     

    Measures 

     

    Last 4 weeks =
    var _max1 = maxx(allselected('Table'), 'Table'[date])
    var _max = maxx(filter(all(date), date[date] = _max1), week[Rank])
    return
    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-4 && 'Date'[Week Rank]<=_max))

     

     

    last 4 week based on today

     


    4 Week today =
    var _st = today() +-1*WEEKDAY(today(),2)+1 - 28
    var _end =today()+ 7-1*WEEKDAY(today(),2)
    return
    CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter

     

     

    Columns you need in date table

     

    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

  • NewbieJono's avatar
    NewbieJono
    Post Partisan
    4 years ago

    Thanks for this, i have a date filter on the page with a single select of days.

     

    at the moment the graph ignores the filter. but can i have the above to take the fitler date and provide results of the last 4 weeks from date selected