Forum Discussion
Time Intelligence: How to perform a loop over different product groups?
Hello
I'm pretty new to Power BI and should use it for some calculations.
I have a single table for about 5000 different product groups with sales data and I want to perform some time intelligence calculations as TOTALYTD. Since I have in the Sales table different products with the same time/date I run into troubles when performing time intelligence functions. It runs perfectly if I choose a single product group, but I'm not willing to perform the same calculations 5000 times manually. Is there any possibility for something like a "FOR EACH Loop"?
Thank you for your answers.
Patrick
Your model looks good to me so thats a great start. Are you trying to create a calculated column or a measure?
If creating a measure it should be just a case of creating a formula that incorprates the right filters
I'm guessing you will need to incorporate all or allexcept within the filters of your formula, for example
=TOTALYTD(SUM(Sales[total_value]),DimDate[DateKey], ALL(‘DimDate’))
Should override any filters/slicers you have on date
The above measure then dynamically picks up its context by the fields as you have defined in the visual (think like a pivot table) so as an example, a column chart could have geography as the axis, product as the legend and the new measure as the value and you would get columns showing sum of total value for the year to date
6 Replies
There's a lot of information on time intelligence over on the DAX Patterns web site:
itchyeyeballsImpactful Individual
Can you outline your table structure and include some sample data, its hard to visualise from your description.
Thank you for your answer so far.
I'm working with the following Data Model:
I need to create some reports on different geographical levels as well as for different products. Therefore I want to perform time intelligence calculations on the most disaggregated level.
Here you find a snapshot of the sales table:
I hope you got the idea of my problem.
Thank you for your answers.
- itchyeyeballs
Your model looks good to me so thats a great start. Are you trying to create a calculated column or a measure?
If creating a measure it should be just a case of creating a formula that incorprates the right filters
I'm guessing you will need to incorporate all or allexcept within the filters of your formula, for example
=TOTALYTD(SUM(Sales[total_value]),DimDate[DateKey], ALL(‘DimDate’))
Should override any filters/slicers you have on date
The above measure then dynamically picks up its context by the fields as you have defined in the visual (think like a pivot table) so as an example, a column chart could have geography as the axis, product as the legend and the new measure as the value and you would get columns showing sum of total value for the year to date
