Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power BI Community,
I have a complex problem and am wondering if Power BI can solve this problem.
I have a data that consists the following.
The data shows how much each product was sold at a particular store at a particular date.
(Price is a price on a shelf)
Due to a regulation change, during a few months,
each store began to change the price on their shelves from with tax price to pre-tax price.
I want to change the price in the data set to all pre-tax price
to see the price evolution on a graph.
However, there is a problem.
Is there a way to adjust the with tax price to pre-tax price even though
the timing is different for each store?
The result I would like to have is:
AA price in month of april and may to be adjusted to pre-tax price (8% tax)
BB price to stay the same
CC price in month of april and may to be adjusted to pre-tax price (8% tax)
DD price in month of april and may and june to be adjusted to pre-tax price (8% tax)
Appreciate your support!
H
Hi,
Try this calculated column formula
=if(CALCULATE(MAX(Data[Price]),FILTER(Data,Data[Product Name]=EARLIER(Data[Product Name])&&Data[Date]<EARLIER(Data[Date])))>Data[Price],Data[Price],Data[Price]/1.08)
Hope this helps.
Thanks for the reply.
However, your suggestion does not work in Power BI.
When I put earlier expression, it does not recognize the product column.
Any suggestion?
Many thanks,
H
Hi,
It works absolutely fine. I do not know what mistake you are committing. Share the download link of your PBI file with your formula.
Hi,
Mine is a calculated column formula (not a measure). When you write it as a calculated column formula, it works well.
Great. This works!
But I do have another problem now.
For a product that is not listed at a store would show a price of 0.
Price on a shelft at a store level occured sometime between May'16 and Aug'16.
Your calculated column works for products that were already listed at a store from May'16 to Aug'16.
But for those products that got listed after Aug'16, already shows pre-tax price on a shelf,
so I don't want these prices to be adjusted.
For example, in screenshot1 below, this product got listed on Aug'17 and already showing pre-tax price of 118.
I do not want to adjust this price at all.
In screenshot2 below,
the price on 2016/11/30, 2016/12/15, 2017/1/13, 2017/3/3, 2017/4/21, 2017/5/19, 2017/5/26, 2017/8/4, 2017/8/8
were adjusted, but these are already pre-tax prices so I do not want to adjust these prices.
How can I solve this problem?
Also I cannot filter out a value of 0 from Price column because I need to use those data for a different visuals.
Screenshot 1:
Screenshot 2:
Many thanks,
H
Hi,
Let's keep things simple. Please share a dataset with your eexpected answers in every cell of a spare column. That way i will be able to compare my result with your expected answer. Share the link from where i can download your file.
Please find below as the dummy data in excel.
I have put my desired outcome and highlighted in orange.
Many thanks,
H
Hi,
In that file, Tempo Name and SKU are in a language which i do not understand. PLease share English Names.
Hi,
Write this calculated column formula
=if(OR(Data[Time Date]>DATE(2016,8,31),CALCULATE(MAX(Data[Price]),FILTER(Data,Data[SKU]=EARLIER(Data[SKU])&&Data[Time Date]<EARLIER(Data[Time Date])))>Data[Price]),Data[Price],Data[Price]/1.08)
Hope this helps.
With my master data, it still wouldn't work.
Could you please retry with this dummy data?
Sorry for the inconvenience.
Many thanks,
Hide
Hi,
I tried my formula on your dummy data yesterday before it posting it here. It was working perfetly yesterday.
Yes, you are right.
It did work with the dummy data that I provided yesterday.
But with my master data (confidential data) I have, because the data is different
it did not work.
I modified the dummy data.
If a new formula works with this dummy data, it should work perfecty with my master data.
Many thanks,
H
Hi,
In the PowerPivot window, click on any cell in the Time Date column and change the data type to Date. My formula should work fine now.
Hi @hidenseek9,
If I understand you correctly, you should be able to use the formula below to create a new calculate column in your table to recalculate the Price accordingly in your scenario.
New Price = SWITCH ( Table1[Product Name], "AA", IF ( MONTH ( Table1[Date] ) = 4 || MONTH ( Table1[Date] ) = 5, Table1[Price] * ( 1 + 0.08 ), Table1[Price] ), "CC", IF ( MONTH ( Table1[Date] ) = 4 || MONTH ( Table1[Date] ) = 5, Table1[Price] * ( 1 + 0.08 ), Table1[Price] ), "DD", IF ( MONTH ( Table1[Date] ) = 4 || MONTH ( Table1[Date] ) = 5 || MONTH ( Table1[Date] ) = 6, Table1[Price] * ( 1 + 0.08 ), Table1[Price] ), Table1[Price] )
Regards
Thank you for your reply.
However, I am afraid the formula does not work because
in the dummy data I provided, I know which month, the price on a shelf
became pre-tax price, but in the real data I have with thousands and thousands of data,
I do not know which month the price changes.
Hence, I need a DAX formula to detect when the price changed and apply the pre-tax calculation on all the previous months.
I hope this is clear.
If you have any question, please let me know.
Many thanks,
H
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |