Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
madeleinebell
Regular Visitor

Calculating Sales by Different Time Periods in One Table

‎09-17-2019 01:26 PM

Hello, I'm new to Power BI and am working to offboard our company's reports from Chartio to Power BI!

 

Right now we have a table in Chartio that shows (dummy data). This updates every week with the relative date.Capture.JPG

I'd like to recreate this in Power BI, but can't seem to find a clean way to build out this table. If I use a relative date slicer (i.e. last week), my table only shows last week's sales and I can't seem to add prior week, YTD, etc from other relative date slicers.

 

How would you recommend I go about building this out? 

 

Labels:
Message 1 of 6
577 Views
0
5 REPLIES 5
v-lili6-msft
Community Support
Community Support

‎09-18-2019 12:59 AM

hi, @madeleinebell 

For your case, you just need to use Time-intelligence functions to create the different measure for prior week, YTD, etc from other relative date slicers.

for example:

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 6
540 Views
0
madeleinebell
Regular Visitor
In response to v-lili6-msft

‎09-18-2019 06:17 AM

Thanks @v-lili6-msft ! So in my raw data, I have a column that is "Week Beginning", as our data is all on a weekly basis. 

 

Here are the DAX functions I've successfully created thus far:

  • Last Week = lastdate('rawdata'[week beginning])
  • Last Week Last Year =  lastdate('rawdata'[week beginning])-364
  • Prior Week = lastdate('rawdata'[week beginning])-7
  • Prior Week Last Year = lastdate('rawdata'[week beginning])-371

 

I can't seem to figure out how to get the following, because we also use NRF weeks-- I do have a data column in my raw data that is NRF week as well if that would help?

  • Last 4 Weeks
  • Last 4 Weeks Last Year
  • Year to Date
  • Year to Date Last Year
Message 3 of 6
529 Views
0
v-lili6-msft
Community Support
Community Support
In response to madeleinebell

‎09-18-2019 06:57 PM

hi, @madeleinebell 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 4 of 6
512 Views
0
madeleinebell
Regular Visitor
In response to v-lili6-msft

‎09-20-2019 06:20 AM

Thanks @v-lili6-msft  Please see below for the sample data in the format we've imported it into Power BI. The table visual I want to build from this would be showing theaggregated  retail revenue dollars for the retailer from Last Week, Week Prior, Year-to-Date This Year, and Year-to-Date Last year, then also showing the % difference for Week-over-Week, Last Week Year over Year, Last 4 Weeks Year over Year, and Year to Date Year over Year. 

retailerproductonline or storequarterretail revenueunits soldweeks per month (nrf)week of month (nrf)month numberyearweek of yearweek endingweek beginning
Retailer AProduct 14OnlineQ3 20181267175292018369/15/2018 12:00:00 AM9/9/2018 12:00:00 AM
Retailer AProduct 16OnlineQ3 2018802165292018369/15/2018 12:00:00 AM9/9/2018 12:00:00 AM
Retailer AProduct 16OnlineQ3 20151240975292015369/12/2015 12:00:00 AM9/6/2015 12:00:00 AM
Retailer AProduct 6StoreQ3 20186188855920183910/6/2018 12:00:00 AM9/30/2018 12:00:00 AM
Retailer AProduct 21OnlineQ3 20182338555920183910/6/2018 12:00:00 AM9/30/2018 12:00:00 AM
Retailer AProduct 4StoreQ3 2017442715192017359/9/2017 12:00:00 AM9/3/2017 12:00:00 AM
Retailer AProduct 18OnlineQ3 20181111495492018389/29/2018 12:00:00 AM9/23/2018 12:00:00 AM
Retailer AProduct 6OnlineQ3 2018279585192018359/8/2018 12:00:00 AM9/2/2018 12:00:00 AM
Retailer AProduct 9OnlineQ3 2018252495192018359/8/2018 12:00:00 AM9/2/2018 12:00:00 AM
Retailer AProduct 24OnlineQ3 2018776785192018359/8/2018 12:00:00 AM9/2/2018 12:00:00 AM
Retailer AProduct 13OnlineQ3 2017112435392017379/23/2017 12:00:00 AM9/17/2017 12:00:00 AM
Retailer AProduct 15StoreQ3 20171351595392017379/23/2017 12:00:00 AM9/17/2017 12:00:00 AM
Retailer AProduct 8StoreQ3 2018690915392018379/22/2018 12:00:00 AM9/16/2018 12:00:00 AM
Retailer AProduct 7OnlineQ3 2017594235292017369/16/2017 12:00:00 AM9/10/2017 12:00:00 AM
Retailer AProduct 14OnlineQ3 20171230655292017369/16/2017 12:00:00 AM9/10/2017 12:00:00 AM
Retailer AProduct 18OnlineQ3 20171370905292017369/16/2017 12:00:00 AM9/10/2017 12:00:00 AM
Retailer AProduct 5OnlineQ3 2017180845292017369/16/2017 12:00:00 AM9/10/2017 12:00:00 AM
Retailer AProduct 21OnlineQ3 2017569644182017318/12/2017 12:00:00 AM8/6/2017 12:00:00 AM
Retailer AProduct 9OnlineQ3 20181378774182018318/11/2018 12:00:00 AM8/5/2018 12:00:00 AM
Retailer AProduct 5OnlineQ3 20191894182019318/10/2019 12:00:00 AM8/4/2019 12:00:00 AM
Retailer AProduct 28OnlineQ3 2019100604182019318/10/2019 12:00:00 AM8/4/2019 12:00:00 AM
Retailer AProduct 16OnlineQ3 20151406145192015359/5/2015 12:00:00 AM8/30/2015 12:00:00 AM
Retailer AProduct 16StoreQ3 2016206304482016349/3/2016 12:00:00 AM8/28/2016 12:00:00 AM
Retailer AProduct 14OnlineQ3 20171156974482017349/2/2017 12:00:00 AM8/27/2017 12:00:00 AM
Retailer AProduct 15StoreQ3 2017642124482017349/2/2017 12:00:00 AM8/27/2017 12:00:00 AM
Retailer AProduct 15OnlineQ3 20181427154482018349/1/2018 12:00:00 AM8/26/2018 12:00:00 AM
Retailer AProduct 21StoreQ3 201874504482018349/1/2018 12:00:00 AM8/26/2018 12:00:00 AM
Retailer AProduct 16StoreQ3 2015840734482015348/29/2015 12:00:00 AM8/23/2015 12:00:00 AM
Retailer AProduct 33StoreQ3 20181830754382018338/25/2018 12:00:00 AM8/19/2018 12:00:00 AM
Retailer AProduct 8OnlineQ3 20181729944382018338/25/2018 12:00:00 AM8/19/2018 12:00:00 AM
Retailer AProduct 16OnlineQ3 20171390834172017277/15/2017 12:00:00 AM7/9/2017 12:00:00 AM
Retailer AProduct 15StoreQ3 20181696304172018277/14/2018 12:00:00 AM7/8/2018 12:00:00 AM
Retailer AProduct 16OnlineQ3 2019388304172019277/13/2019 12:00:00 AM7/7/2019 12:00:00 AM
Retailer AProduct 10OnlineQ3 20191458604172019277/13/2019 12:00:00 AM7/7/2019 12:00:00 AM
Retailer AProduct 16OnlineQ3 20161130114472016308/6/2016 12:00:00 AM7/31/2016 12:00:00 AM
Retailer AProduct 8OnlineQ3 20171133974472017308/5/2017 12:00:00 AM7/30/2017 12:00:00 AM
Retailer AProduct 21OnlineQ3 20171008194472017308/5/2017 12:00:00 AM7/30/2017 12:00:00 AM
Retailer AProduct 13OnlineQ2 2016495985562016267/9/2016 12:00:00 AM7/3/2016 12:00:00 AM
Message 5 of 6
493 Views
0
v-lili6-msft
Community Support
Community Support
In response to madeleinebell

‎09-23-2019 02:24 AM

HI, @madeleinebell 

You could try this way:

Step1:

Create a date table, then create the relationship with basic data table by [week beginning]

Step2:

Create the measure as below:

Last Week = CALCULATE(SUM('Table'[retail revenue]),DATEADD('Date'[Date],-7,DAY))
lastyear last year = CALCULATE([Last Week],SAMEPERIODLASTYEAR('Date'[Date]))

and here is sample pbix, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sample.pbix
Message 6 of 6
481 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All