Time Intelligence: How to perform a loop over different product groups?

[Patrick_Knobel]

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

[Anonymous]

There's a lot of information on time intelligence over on the DAX Patterns web site:

http://www.daxpatterns.com/time-patterns/

[itchyeyeballs]

Can you outline your table structure and include some sample data, its hard to visualise from your description.

[Patrick_Knobel]

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 · Accepted solution]

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

[Patrick_Knobel]

Thanks for your help.

I've tried to do a calculated column which did not work out, but as measure it does. So the problem is solved.

Thanks again

[greggyb]

Calculated columns are to be used for data that does not change between dataset refreshes. Calculated columns are not dynamic in the context of report interaction. Any sort of aggregation like this should be a measure, which will respond appropriately to filter context defined in the report.