Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hidenseek9
Post Patron
Post Patron

DAX to automatically adjust the price from with tax to pre-tax

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.

  • Date
  • Store Name
  • Product Name
  • Price

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.

 

DummyData

 

However, there is a problem.

  1. Not all stores changed the price on the same month. Some changed the price in May, some changed in June, others changed in July

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

18 REPLIES 18
Ashish_Mathur
Super User
Super User

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

Thanks for the reply.

However, your suggestion does not work in Power BI.

 

2017-12-25 12_21_52-TEMPO Data - Power BI Desktop.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

Please find below.

DummySample PBI

 

Appreciate your support.

 

Many thanks,

 

H

Hi,

 

Mine is a calculated column formula (not a measure).  When you write it as a calculated column formula, it works well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

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:

2017-12-26 10_39_09-TEMPO Data - Power BI Desktop.png

 

Screenshot 2:

2017-12-26 10_51_52-TEMPO Data - Power BI Desktop.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

Please find below as the dummy data in excel.

I have put my desired outcome and highlighted in orange.

 

Dummy Data

 

Many thanks,

 

H

Hi,

 

In that file, Tempo Name and SKU are in a language which i do not understand.  PLease share English Names.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

Please check below.

 

Dummy Data

 

Many thanks,

 

H

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

With my master data, it still wouldn't work.

 

Could you please retry with this dummy data?

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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

c1.PNG

 

Regards

@v-ljerr-msft

 

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

@v-shex-msft

 

Do you think you can solve this issue, please?

 

Thanks,

 

H

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.