Forum Discussion

shivkonar's avatar
shivkonar
Frequent Visitor
7 years ago
Solved

Bar Chart / Date Calculation

Hi,   I am trying to solve a made up problem. Imagine we have a 2 table model. The Sales table and a Date Table in a 1 to Many relationship.   On the Power BI report, there is a slicer for Date[C...
Need Help
Tutorial Requests
  • shivkonar's avatar
    shivkonar
    7 years ago

    Thanks v-juanli-msft

     

    There is a more elegant solution by Marco Russo.

     

    Step 1:- Create a new DateSelection table, this table will be placed on the slicer

    DateSelection = DISTINCT ( 'Date'[Calendar WeekNumber] )

    Step 2:- Create the measure

    Last 4 Week Sales = 
VAR LastWeek = SELECTEDVALUE ( 'DateSelection'[Calendar WeekNumber] )
VAR FirstWeek = LastWeek - 3
VAR Result =
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        KEEPFILTERS (
            'Date'[Calendar WeekNumber] >= FirstWeek
                && 'Date'[Calendar WeekNumber] <= LastWeek
        )
    )
RETURN Result

    Step 3:- Create the bar chart, by placing Date[Calendar WeekNumber] on the axis and [Last 4 Week Sales] on the values.

     

    So, now for any week selected, the bar chart will show 4 bars, one bar for the selected week and 3 previous week.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi shivkonar

Please have a look at my method.

1.create relationship between sales table(sheet3) and date table(sheet4)

2. add "weeknum1" from the date table to the slicer, then create measure in the date table

selected week = SELECTEDVALUE(Sheet4[weeknum1])

3.create measures in the sales table

total sales =
CALCULATE (
    SUM ( Sheet3[sales] ),
    FILTER ( ALL ( Sheet3 ), [weeknum2] = SELECTEDVALUE ( Sheet3[weeknum2] ) )
)

flag = IF(MAX([weeknum2])<=[selected week]&&MAX([weeknum2])>[selected week]-4,1,0)

4.add "weeknum2" column from the sales table to the Axis of the bar chart, then add "flag" measure to the Visual filter and select 1 to apply filter.

 

Best Regards

Maggie

shivkonar's avatar
shivkonar
Frequent Visitor
7 years ago

Thanks v-juanli-msft

 

There is a more elegant solution by Marco Russo.

 

Step 1:- Create a new DateSelection table, this table will be placed on the slicer

DateSelection = DISTINCT ( 'Date'[Calendar WeekNumber] )

Step 2:- Create the measure

Last 4 Week Sales = 
VAR LastWeek = SELECTEDVALUE ( 'DateSelection'[Calendar WeekNumber] )
VAR FirstWeek = LastWeek - 3
VAR Result =
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        KEEPFILTERS (
            'Date'[Calendar WeekNumber] >= FirstWeek
                && 'Date'[Calendar WeekNumber] <= LastWeek
        )
    )
RETURN Result

Step 3:- Create the bar chart, by placing Date[Calendar WeekNumber] on the axis and [Last 4 Week Sales] on the values.

 

So, now for any week selected, the bar chart will show 4 bars, one bar for the selected week and 3 previous week.