The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a dataset with all actual sales from 2017 to date by year, week & product. For each combination I have a Volume and a Value. I also have corresponding targets.
The data will be refreshed weekly with the latest week's worth of data added.
The requirement is to have a report object with different timeslices of the data. For example I need to include the following:
Year to Date Volume and Value (also split by product)
Current Week Volume and Value (split by product)
Year to Date Volume and Value vs Target
Current Week Volume and Value vs Target
etc. etc. you get the idea.
These calculations need to be dynamic as well, so they should automatically update to the latest report week when the data is refreshed.
The way I have satisifed this requirement is to create a load of calculated measures using SUMX and FILTER, although this has taken a lot of time the results are satisfactory.
I just hope there is a more efficient way of doing this which is where you guys come in? What approaches do you use?
Apologies if this has been asked before, I have searched but cannot find it.
Solved! Go to Solution.
Hi Lydia,
Thanks for your replies so far.
I manged to solve this through the addition of a date column which related to the year and week no as per this thread http://community.powerbi.com/t5/Desktop/Weekly-data-can-you-still-use-Time-Intelligence-functions/m-...
@Anonymous,
You can calculate Year to date values using TOTALTYD() function or use quick measure following the guide in this article.
To calculate current week value, please check my reply in this similar thread, or you can use relative date slicer to filter your visuals.
If you have questions about DAX, please show dummy data of your table.
Regards,
Lydia
@Anonymous,
Please share dummy data of your table for us to analyze.
Regards,
Lydia
Please see the sample data of actuals and targets attached.
For the data sets I have 2 records for each combination of Year and Week No (Value and Volume)
The main limitation I am finding is that because the data is at Week No. and not day I cannot utilise a Date table in order to use the DAX functions.
@Anonymous,
What field do you use to create relationship between the two tables? Could you please post complete data in Excel file and share the Excel file through OneDrive?
Regards,
Lydia
Hi Lydia,
Thanks for your replies so far.
I manged to solve this through the addition of a date column which related to the year and week no as per this thread http://community.powerbi.com/t5/Desktop/Weekly-data-can-you-still-use-Time-Intelligence-functions/m-...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |