Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shivkonar
Frequent Visitor

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

1 ACCEPTED SOLUTION

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.

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

I don't see any bars on the chart. No matter what week I select.

 

Screen Shot 2018-08-25 at 13.37.03.png

 

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

Screen Shot 2018-08-25 at 13.38.29.png

 

And here is the measure:

Screen Shot 2018-08-25 at 13.40.32.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur,

 

I have made the relationship as you suggessted. Still no bars

 

Screen Shot 2018-08-26 at 07.58.37.png

 

Screen Shot 2018-08-26 at 08.01.15.png

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

Here you go:- https://drive.google.com/drive/folders/19Qw-n4gV4ncsffU-PV58_PUI8PgmlRLu?usp=sharing

 

Page 1 is Marco's solution

 

Page 2 and [Last 4 Week Sales5] are your solution.

 

Hi,

 

I am unable to solve the problem with my formula.  I believe you should stick to Marco's solution.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@shivkonar

Couple points...

1) Datesbetween needs a date column Not a weeknumber. Also read this about Dates in PBI (why you are getting nothing)

2) But even if you use a date column using 28 (or 4 weeks times 7 days) will not give you 4 complete weeks but rather 28 consecutive days which is not the same. 

Stick with the Marco Russo solution! Smiley Happy

v-juanli-msft
Community Support
Community Support

Hi @shivkonar

Please have a look at my method.

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

5.png

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)

6.png

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.

7.png

 

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 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.