Forum Discussion
Calculating Sales by Different Time Periods in One Table
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.
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?
5 Replies
v-lili6-msftCommunity Support
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
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
- v-lili6-msft
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/38490Best Regards,
Lin
