March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
I have an Excel workbook with reference data - i.e. Days of the Year, Week#, Month# etc...
Other tabs bear data for certain processes, i.e. Sales etc.. which are date related.
I want to use a date slicer and roll up data so that when I look at Feb as a month, it gives me the total of all Sales and other departments etc... The common theme is the date but I need to create Dax queries to compute things like revenue / expenses etc... which I can manage.
Please help.
Thanks,
S
Solved! Go to Solution.
Hi,
So I used the Append Query. I made sure my date fields in my data source were broken down for Day/Week/Month/Year for each tab in my Excel Spreadsheet. I then used the append query by making sure the date, i.e. week (as I wanted all data to be by week) was joined and replicated for all tabs so that the append query (mash up of all Excel tabs) was consistent. The Append Query then showed all fields across all tabs in my Excel sheet. This then enabled me to add slicers for Week in my report.
Thanks.
Hi @SachinC,
In your Excel Workbool, there are different sheet including Date, Sales and so on? Based on your description, I am unable to reproduce your scenario, could you please share more details or sample data for further analysis?
For appending and merging queries, please click the button highlighted in red line in following screenshot. More details, please review this article.
Best Regards,
Angelia
hi,
heres more details. im using an excel workbook.
tab1
------
reference data, ie week dates 01/01/2017, 08/01/2017.....
week no: 1,2....
month#:1,2,3....
quarter:1,2...
year:2016,2017
tab2
-----
opportunity data, i.e,opportunity name
date: random dates
amount£
tab3
-----
project name
date
resource
i want to use the reference data tab for my filters and roll up all other data so if i filter for week 13, as an example, it gives me a cumulative viewpoint as at week 13.
pls help ?
thank you
Hi @SachinC,
When you filter for week 13, you want a cumulative viewpoint including datas in tab1, tab2, tab3? In Power BI, please load all resource data in tab1, tab2, tab3 as three different tables. If there are raltionship between them, you can create a slicer including week to filter data, you only get the corresponding rows related to week 13.
While, in your given table, there is no commom field in three table. How to create relationship. If you want get all data at week 13 without relationship among them, we are unable to achieve. Thanks for understanding.
Best Regards,
Angelia
Yes, thanks for that. I figured this out eventually myself.
Hi @SachinC,
I am very glad to hear that you have resolve your issue, could you please share your solution which will help more people?
Best Regards,
Angelia
Hi,
So I used the Append Query. I made sure my date fields in my data source were broken down for Day/Week/Month/Year for each tab in my Excel Spreadsheet. I then used the append query by making sure the date, i.e. week (as I wanted all data to be by week) was joined and replicated for all tabs so that the append query (mash up of all Excel tabs) was consistent. The Append Query then showed all fields across all tabs in my Excel sheet. This then enabled me to add slicers for Week in my report.
Thanks.
Hi @SachinC,
Thanks for your detailed information. Please mark your solution as answer, so other people will find solution easily.
Best Regards,
Angelia
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |