Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi community
My objective is to compare sales for multiple products with each having different launch dates.
I want to be able to choose eg. 2 products and compare the sale of them by days after launch.
The problem is that the launch date has to be user input and not a calculated column.
The final result should be a graph as follows:
X-axis: Days after launch
Y-Axis: Qty
Legend: Product name
I have experimented with field parameters and making multiple measures for each slicer without succes.
The graph below should only contain the products from slicers "Product " 1 and 2.
The days after launch for each product should be calculated based on the "Launch date associated to product x"
Ultimately looking like this (Source: https://community.powerbi.com/t5/Desktop/Comparing-Product-Life-Cycle-of-different-products-Cumulati...)
But with the ability to choose products and launch date as in the first picture
I havent found any solutions in this community without a fixed product launch date.
I'm starting to think that it is not possible to do in Power BI.
I have attached a .pbix file
Any ideas are welcome and very appreciated!!!
Solved! Go to Solution.
Hi @MathiBBN ,
To do this you need to make the same thing you did for the slicers of the products, create 3 tables with the dates then change your measure to:
Product comparison = VAR FirstSold =
CALCULATE (
MIN ( Sales[Sell_Date] ),
Products[ProductName]
IN {
SELECTEDVALUE ( 'Product 1'[ProductName] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),
SELECTEDVALUE ( 'Product 3'[ProductName] )
}
)
VAR SelectionFirstDate =
SWITCH( MAX( Products[ProductName]),
SELECTEDVALUE ( 'Product 1'[ProductName] ),MIN ( Product1Date[Date] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),MIN ( Product2Date[Date] ),
SELECTEDVALUE ( 'Product 3'[ProductName] ),MIN ( Product3Date[Date] ),
FirstSold)
VAR LastSold =
CALCULATE (
MAX ( Sales[Sell_Date] ),
Products[ProductName]
IN {
SELECTEDVALUE ( 'Product 1'[ProductName] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),
SELECTEDVALUE ( 'Product 3'[ProductName] )
}
)
VAR days =
SELECTEDVALUE ( 'Days after launch'[Days] )
RETURN
CALCULATE (
Sales[Qty],
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= SelectionFirstDate + days
&& SelectionFirstDate + days <= LastSold
),
FILTER (
Products,
Products[ProductName]
IN {
SELECTEDVALUE ( 'Product 1'[ProductName] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),
SELECTEDVALUE ( 'Product 3'[ProductName] )
}
)
)
You can also adjust the formula to pick up the sales first day if nothing is selected on the filter, if you need help please reach out.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MathiBBN
Without data is difficult to pinpoint, but believe you need a specific metric.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thank you for your reply.
Here is a link to a sample file that resembles my data model: https://we.tl/t-W32vBnJz2W
On page 2 i have tried creating a measure that "resets" sales dates to an x-axis of Days after launch.
Without any luck though.
Again the objective is to choose eg. 3 products, having the user set a launch date for each product, and then comparing sales from those dates as a function of days after launch.
Any help would be much appreciated!
Hi @MathiBBN
Believe that you have an error on your calculation you have the following:
FILTER (
ALL ( 'Date' ),
'Date'[Date] = FirstSold + days
&& FirstSold + days <= LastSold
)
Believe this should be:
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= FirstSold + days
&& FirstSold + days <= LastSold
)
Final result is this:
Is this the result you pretend?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
yeah that looks better.
My problem though is that i want the user to input dates for each product.
days after launch should be calculated based on this user input for each product.
Do you have any ideas how to do that?
Just to understand
You want to add a slicer for each product with the start date?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix
Thank you so much for your quick replies!!!
Yes exactly, having a user input start date for each product.
An example could be that i want to compare the sales of 3 products after a user defined launch date.
Product A should be from 1-1-2021
Product B should be from 1-2-2022
Product C should be from 1-3-2023
Days after launch should then count the days after these dates set by the user.
I have seen it done in other data modeling tools than Power BI.
Hi @MathiBBN ,
To do this you need to make the same thing you did for the slicers of the products, create 3 tables with the dates then change your measure to:
Product comparison = VAR FirstSold =
CALCULATE (
MIN ( Sales[Sell_Date] ),
Products[ProductName]
IN {
SELECTEDVALUE ( 'Product 1'[ProductName] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),
SELECTEDVALUE ( 'Product 3'[ProductName] )
}
)
VAR SelectionFirstDate =
SWITCH( MAX( Products[ProductName]),
SELECTEDVALUE ( 'Product 1'[ProductName] ),MIN ( Product1Date[Date] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),MIN ( Product2Date[Date] ),
SELECTEDVALUE ( 'Product 3'[ProductName] ),MIN ( Product3Date[Date] ),
FirstSold)
VAR LastSold =
CALCULATE (
MAX ( Sales[Sell_Date] ),
Products[ProductName]
IN {
SELECTEDVALUE ( 'Product 1'[ProductName] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),
SELECTEDVALUE ( 'Product 3'[ProductName] )
}
)
VAR days =
SELECTEDVALUE ( 'Days after launch'[Days] )
RETURN
CALCULATE (
Sales[Qty],
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= SelectionFirstDate + days
&& SelectionFirstDate + days <= LastSold
),
FILTER (
Products,
Products[ProductName]
IN {
SELECTEDVALUE ( 'Product 1'[ProductName] ),
SELECTEDVALUE ( 'Product 2'[ProductName] ),
SELECTEDVALUE ( 'Product 3'[ProductName] )
}
)
)
You can also adjust the formula to pick up the sales first day if nothing is selected on the filter, if you need help please reach out.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you so much, i can see in the sample file that it works as intended.
However i am getting this error in my own dashboard.
Maybe the measure is too computationally expensive for Power BI when there is more data?
I have tried limiting it to 2 products, but i get the same error.
Any ideas?
Hi @MathiBBN,
Is this on the service or on PBI Desktop?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
76 | |
67 | |
61 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |