Forum Discussion
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[Calendar Year] = 2018 and another slicer for Date[Calendar WeekNumber] and there is a bar chart.
The bar chart always will only show 4 bars, for example. The 4 bars will show [Total Sales] for 4 weeks from the selected Date[Calendar WeekNumber] going backwards.
Eg: User selects Date[Calendar WeekNumber] = 31
Bar chart will show 4 bars:
Bar 1: Total Sales for WeekNumber 28
Bar 2: Total Sales for WeekNumber 29
Bar 3: Total Sales for WeekNumber 30
Bar 4: Total Sales for WeekNumber 31
My approach was a "New Table" route in Power BI. The formula I came up with is as below:
Past 4 Weeks Sales =
GROUPBY (
FILTER(
ALL('Sales'),
RELATED('Date'[Calendar WeekNumber]) >= SELECTEDVALUE('Date'[Calendar WeekNumber]) - 3 &&
RELATED('Date'[Calendar WeekNumber]) <= SELECTEDVALUE('Date'[Calendar WeekNumber])),
'Date'[Calendar WeekNumber],
"Total Sales", SUMX ( CURRENTGROUP (), 'Sales'[Sales] )
)This works when I hardcode the >= with 28 and <= with 31.
Question:
1. What is wrong with my approach? Why does this always return a blank table ? And why does this work with hard-coded conditions
2. Is there a more elegant way to solve this problem?
Thanks,
Shiv
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 ResultStep 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.
10 Replies
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
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 ResultStep 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.
Hi,
With the relationship well set, try this measure
=CALCULATE(SUM('Sales'[Sales]),DATESBETWEEN('Date'[Date],MAX('Date'[Calendar WeekNumber]),MAX('Date'[Calendar WeekNumber])-28))
Hope this helps.
I don't see any bars on the chart. No matter what week I select.
Here is my data model: The 'DateSelection' table is not used in your calculated measure and Calculations table is just me storing the measures. There is a 1-Many relationship between Sales and Date table
And here is the measure:
Any ideas why this doesn't work?
Hi,
The relationship should be from the Order Date column of the Sales Table to the Date column of the Date Table.
