Multiple dynamic slicers within same report object?

[Anonymous]

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.

[v-yuezhe-msft]

@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]

Thanks for your reply.

My understanding was that TOTALTYTD() requires a date time column as a parameter? I only have week number and year.

The approach I have adopted is very similar to your response in the thread.

I don't think sliders meet the requirements as I need to make the date filters dynamic depending on when the report is refreshed. So for example the filters all reference the current reporting week.

[v-yuezhe-msft]

@Anonymous,

Please share dummy data of your table for us to analyze.

Regards,
Lydia

[Anonymous]

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)

ActualsTargets

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.

[v-yuezhe-msft]

@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

[Anonymous · Accepted 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-...