Creating dynamic Power BI Parameters for Week on Week comparisons

[Anonymous]

Hi, its been a short while since I've been using What-if Parameters in Power BI. 

Though I am struggling when I want to make week on week/ month on month comparisons, in cases where I require the parameter value to be different across weeks/months. 

Below is an example: 

Expected Sales Parameter = 2 for Week 1

                                             3 for Week 2 and so on.

*Expected Sales is manually inputted by the user in the parameter filter. 

Basis Stock and Sales I am computing a 'Cover':

Cover = Stock / (Sales * Expected Sale Factor)  

Eg. Wk 1, Product A, Cover = 100/ (50 * 2) = 1. 

Note the nature of Expected Sales (and even discounts in other cases) is not static week on week (or month on month). 

As of now, as a way around this, I am looking at my data by selecting only one week and setting the Expected Sales parameter to give me the cover of that week. 

However, this means that I cannot compare the Cover of current week and previous, or create any trend visualisations on the Cover. 

Is there a way around using parameters or any other solution.  Thanks! 

[amitchandak]

@Anonymous , For Week and WOW , refer to My blog, See if that can help.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

If not

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak , appreciate the support. 

Attaching the sample Input & desired Output in an Excel. Also, sharing a pbix showing what I am able to achieve (against the desired output in Excel).  https://acxiomconsulting-my.sharepoint.com/:f:/r/personal/siddharth_chowdhury_acxiomconsulting_com/D... 

[amitchandak]

@Anonymous , No access to file. Please share it again. Also can you let us know the issue you facing in approch

[Anonymous]

https://acxiomconsulting-my.sharepoint.com/:f:/p/siddharth_chowdhury/EgTsItIX1rtGrJzezHeHXx0BeuSssPh... 

Hi, 

please try the above link. 

The issue is that I want to allow my end user on Power BI service to input values for 'Expected Sales Factor'. Basis this factor, we calculate the 'Cover'.

For this purpose, I took the Parameter approach. However, this factor needs to be different every week (I might expect my sales to double in the current week, opposed to more than double in the next week). 

As long as I am looking at one week only at a time, I am good to go - I can set the parameter to what I wish and see what is my Cover for that week. However, if I want to make a week on week comparison (say, show the Cover on a line graph with weeks on the axis), this falls apart. Since I can't input different values in the parameter when looking at more than one week at the same time.

[v-eachen-msft]

Hi @Anonymous ,

If you want multiple parameters for each week, you could save them in a table and match them when you are ready to calculate the "Cover".

Or you could create multiple independent slicers to select parameters with SELECTEDVALUE() function.

You could also vote for this idea to improve Power BI and make this feature coming sooner.

[AllisonKennedy]

I'm thinking you could create a parameter table of sorts, that has the values for each week, but not sure how feasible that is as it sounds like this might change a lot? You could look at integrating PowerApps into the report to make it more interactive as another alternative. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/powerapps-custom-visual