DAX Formula To Determine Discount Based On Date (Q1,Q2,Q3,Q4)

[muhammad susanto]

hi all..
how to determine discount for 3 items Car, Trucks, Bus
this rule:
- my date in dd-mm-yyyy (format date)
- my target in col "car", trucks, bus with different discount
- the discount same rate for all regions
for Car:
- in Quarter 1 (<=1/03/2022) -- get discount 5%
- in Quarter 2 (1/4/2022 till 1/6/2022), --- get discount 7%
- in Quarter 3 (1/7/2022 till 1/9/2022), --- get discount 8%
- in Quarter 4 (1/10/2022 till 1/12/2022), --- get discount 10%
for Trucks:
- in Quarter 1 (<=1/03/2022) -- get discount 10%
- in Quarter 2 (1/4/2022 till 1/6/2022), --- get discount 12%
- in Quarter 3 (1/7/2022 till 1/9/2022), --- get discount 20%
- in Quarter 4 (1/10/2022 till 1/12/2022), --- get discount 25%
-etc..

raw data formula power bi.xlsx
	A	B	C	D	E	F	G	H	I
1	original/raw data						desired result/after formula
2	Region	Date (dd-mm-yyyy)	Car	Trucks	Bus		Car	Trucks	Bus
3	Region A	01/03/2022	0	0	0		5%	10%	20%
4	Region A	01/04/2022	0	0	0		7%	12%	25%
5	Region A	01/05/2022	0	0	0		7%	12%	25%
6	Region A	01/06/2022	0	0	0		7%	12%	25%
7	Region A	01/07/2022	0	0	0		8%	20%	30%
8	Region A	01/08/2022	0	0	0		8%	20%	30%
9	Region A	01/09/2022	0	0	0		8%	20%	30%
10	Region A	01/10/2022	0	0	0		10%	25%	35%
11	Region A	01/11/2022	0	0	0		10%	25%	35%
12	Region A	01/12/2022	0	0	0		10%	25%	35%
13	Region B	01/03/2022	0	0	0		5%	10%	20%
14	Region B	01/04/2022	0	0	0		7%	12%	25%
15	Region B	01/05/2022	0	0	0		7%	12%	25%
16	Region B	01/06/2022	0	0	0		7%	12%	25%
17	Region B	01/07/2022	0	0	0		8%	20%	30%
18	Region B	01/08/2022	0	0	0		8%	20%	30%
19	Region B	01/09/2022	0	0	0		8%	20%	30%
20	Region B	01/10/2022	0	0	0		10%	25%	35%
21	Region B	01/11/2022	0	0	0		10%	25%	35%
22	Region B	01/12/2022	0	0	0		10%	25%	35%
23	Region C	01/03/2022	0	0	0		5%	10%	20%
24	Region C	01/04/2022	0	0	0		7%	12%	25%
25	Region C	01/05/2022	0	0	0		7%	12%	25%
26	Region C	01/06/2022	0	0	0		7%	12%	25%
27	Region C	01/07/2022	0	0	0		8%	20%	30%
28	Region C	01/08/2022	0	0	0		8%	20%	30%
29	Region C	01/09/2022	0	0	0		8%	20%	30%
30	Region C	01/10/2022	0	0	0		10%	25%	35%
31	Region C	01/11/2022	0	0	0		10%	25%	35%
32	Region C	01/12/2022	0	0	0		10%	25%	35%
Sheet1

how to figure out this problem?

susant

 

[alansidman]

Mcode Use this same concept for Busses

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date (dd-mm-yyyy)", type date}}),
    #"Inserted Quarter" = Table.AddColumn(#"Changed Type", "Quarter", each Date.QuarterOfYear([#"Date (dd-mm-yyyy)"]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Car", each if[Quarter]=1 then .05 else if [Quarter]= 2 then .07 else if [Quarter]= 3 then .08 else .1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Truck", each if[Quarter]=1 then .1 else if [Quarter]= 2 then .12else if [Quarter]= 3 then .20 else .25),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Car", Percentage.Type}, {"Truck", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Quarter"})
in
    #"Removed Columns"

 

[muhammad susanto]

hi alan, thank you so much..working great!!!