Using IF to multiply columns according to date

[eliasayy]

Hello, i am trying to multiply 2 columns say cost and number of sales according to the date. so if date is before 2022, the cost is 10 and fixed but if the date is in 2022 or later, i calculate the given cost in the column.

[SpartaBI · Accepted solution]

@eliasayy missing info about your model, but let's try (assuming you have all the columns in one table):
In case yor need a calculate column than adjust this code to your model needs:

Column = Table[Quantity] * IF( YEAR('Table'[Date]) < 2022, 10, Table[Cost])

In case it's a measure go with this:

Measure = 
SUMX(	
    'Table',
    Table[Quantity] * IF( YEAR('Table'[Date]) < 2022, 10, Table[Cost])
)

 
In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

[eliasayy]

like i have sales per day in this table but cost in another table

[eliasayy]

Thank you very much, all data are in the same table. but what if data are in different table. and for more clarity, in a table, i have cost and sales per day, i want to find cost per day. before 2022, the cost was fixed at 10$ but after 2022, i have variable cost everyday they change

[SpartaBI]

@eliasayy  I need more info. Can you share a sample data (in tables, not pictures) but with all the columns in the table and the relationship between the two tables?

[eliasayy]

the thing is the data is confidential i will try to explain.

i have 2 tables. both contain sales per day and only table one contains the cost.

i need to find the cost per day which is cosst*sales per day, but if date <2022 it was 10$ fixed, but after 2022, i have a different cost everyday and have to multiply the cost column with the sales per day column but its in different table. i created a realationship between the tables which is the date is the rlationship

[SpartaBI]

@eliasayy still not sure 😞
Can you maybe create a fake data that you could upload as tables here, and also show exactly what is the relationship (who is the one side who is the many)

[eliasayy]

ok i will try but im fairly new here so how do i create a table here?

[SpartaBI]

@eliasayy you can create it in excel and just copy paste here.

P.S. I will maybe be able to get to only tomorrow but we'll try today. Already late evening here 🙂

[eliasayy]

The relationship is date i managed the relationship as the date

Table 1							Table 2
	date	cost	sales	cost per day				date	sales	cost per day
	10/5/2021	"cost is fixed at 10 because <2022"	70	"=" cost *sales per day				30/4/2021	70	"="  table1[cost] *sales per day
	10/20/2021		40					9/29/2021	67
	1/5/2022	3	21					1/5/2022	50
	9/4/2022	6	68					9/4/2022	190
	9/5/2022	7	55					9/5/2022	90
	9/6/2022	3	65					9/6/2022	55
	9/7/2022	4	89					9/7/2022	120

[SpartaBI]

@eliasayy what is the granulrity of the relationship? 1:1 , 1:m? In case it's 1:m who is the 1 and who is the mant? in case it's 1:1 why don't you just join the tables?

[eliasayy]

im not gonna lie to you, i have no idea what you meant, i am new to dax abd stuff

[SpartaBI]

@eliasayy  No worries. I will pm you

[eliasayy]

very much appreciated