topic Re: Calculate sales target per month combination of 2 different stores in 1 in DAX Commands and Tips

Calculate sales target per month combination of 2 different stores in 1

Anonymous — Thu, 05 May 2022 15:27:51 GMT

Hi all,

Hope you can help out.

I am trying to generate sales targets for our newly opened store last year August.

The sales target for this store needs to be a combination of the sales from another store for the months 1-7 of 2021 and sales from the new store month 8-12 of 2021.

I have data like in the below table. What I would like to have in a list is Sales target for store 2

date	store	sales
jan-21	store1	655000
feb-21	store1	145000
mrt-21	store1	255222
apr-21	store1	365653
mei-21	store1	756241
jun-21	store1	655000
jul-21	store1	145000
aug-21	store2	255222
sep-21	store2	365653
okt-21	store2	756241
nov-21	store2	255222
dec-21	store2	365653

I've been trying to use below DAX code, but with the contains function, all stores get filtered out except store 2.

Order omzet YTD = TOTALYTD([Order omzet totaal],Kalender[Datum])

IF(

CONTAINS(FilialenTabel,FilialenTabel[Fil.code],"store2"),

CALCULATE([Order omzet YTD], Kalender[Maand] IN { 1,2,3,4 },Kalender[Jaar] IN { 2020 }, FilialenTabel[Fil.code]="store1") +(

CALCULATE(

[Order omzet YTD],

Kalender[Maand] IN { 5,6,7 },Kalender[Jaar] IN { 2021 }, FilialenTabel[Fil.code] ="store1")*1.10)

CALCULATE(

[Order omzet YTD],

Kalender[Maand] IN { 8,9,10,11,12 },Kalender[Jaar] IN { 2021 }, FilialenTabel[Fil.code] ="store2")*1.10)

CALCULATE([Order omzet YTD], Kalender[Maand] IN { 1,2,3,4 },Kalender[Jaar] IN { 2020 })+(

CALCULATE(

[Order omzet YTD],

Kalender[Maand] IN { 5,6,7,8,9,10,11,12 },Kalender[Jaar] IN { 2021 })*1.10)

)

Hope someone can help. Thanks in advance.

Re: Calculate sales target per month combination of 2 different stores in 1

johnt75 — Thu, 05 May 2022 15:53:01 GMT

You could try creating a new table using

Target store 2 = UNION( CALCULATETABLE( 'Table', 'Table'[store] = "store 1", DATESBETWEEN('Date'[Date], DATE(2021,1,1), DATE(2021,7,31))), CALCULATETABLE( 'Table', 'Table'[store] = "store 2", DATESBETWEEN('Date'[Date], DATE(2021,8,1), DATE(2021,12,31))) )

Re: Calculate sales target per month combination of 2 different stores in 1

Anonymous — Wed, 11 May 2022 09:45:13 GMT

Hi Anonymous ,

I have checked your code, and I think you seems to calculate the TotalYTD based on some calculation logic.

Is "FilialenTabel" the table your provided above? Is [Fil.code] = [Store] as above? I am confused about why you will use value in 2020/1/2/3/4 in your code.

Could you share a sample file with us and show us a screenshot with the result you want? This will make me easier to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.