Weekly sales graph, per day based on selected date

[Anonymous]

Hello all geniuses!

I am working on some sales overview dashboard within retail. The source is more or less real-time and both the cube and several reports are working fine. My main issue now is related to some time-intelligence for weekly sales, based on selected day.

My tables, to simplify significantly, is a sales table and a calender table. All regular sales measures are working fine. What I am struggeling to figure out now is the two graphs illustrated below;

1. Revenue per day: My goal here is to show actual revenue for each day, where the x-axis always show Mon - Sun. Which week it shall show is depended on which date is selected in the [Date] slicer. If 17 Oct 2018 is selected, I want the graph to show sales per day for the whole week related to that date, which is week number 42. Bonus feature would be to have a second line for sales per day, for the same weeknumber, in the previous year.

2. Revenue per week: This graph is kinda connected to the one above, but the key here would be to show the sum sales for the selected week in the middle, and then offset two weeks both ways (-2, -1, selected week, +1, +2). The dream would be to have this dynamic based on the selected date from the date slicer. Sum sales this year in orange and sum same weeknumbers last year.

Quite a Hail Mary, but thanks in advance for helping out!

[v-yuta-msft · Accepted solution]

Hi Zarius,

You may create a measure using DAX formula like pattern below:

Result =
VAR weeknum =
    CALCULATE (
        MAX ( Table[weeknum] ),
        FILTER ( ALL ( Table ), Table[Date] = SELECTEDVALUE ( Table[Date] ) )
    )
RETURN
    IF ( MAX ( Table[weeknum] ) = weeknum, MAX ( Table[Date] ), BLANK () )

Regards,

Jimmy Tao