cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate with String xpression

Hi Team,

Need help here,  want to calculate Current Week products lists and Last Year Current Week  product list in the same table chart when i select current week from the slicer. Please find the sample data and expected output.

I had tried to use calculate with CONCATENATEX for expression but it gives result as blank rows.

Sample Data:

 Week Product ItemID 201942 Apple 1 201942 Orange 2 201942 Banana 3 201842 Avocado 4 201842 Bilberry 5 201842 Blackberry 6

Expected Output:

 Week Slicer 201942 Current Week Products Curretn Week LY Products Apple Avocado Orange Bilberry Banana Blackberry
1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: Calculate with String xpression

See the attached PBIX.  But an overview:

1. Create a Date table ( please look at Power Query, there's a small function that does that)
2. Need to add YearWeek, Year, and Month columns
3. Load both tables, and relate to each other

Then the following measures:

```Current Week Products =
CALCULATE(
CONCATENATEX( FactTable, FactTable[Product],UNICHAR(10),
ALLSELECTED (DimCalendar[YearWeek])
)
)

Previous Week Products =
CALCULATE(
CONCATENATEX( FactTable, FactTable[Product],UNICHAR(10)),
FILTER (
ALL ( DimCalendar),
MAX(DimCalendar[Year] ) -1 = DimCalendar[Year]
&& Max( DimCalendar[Week of Year]) = DimCalendar[Week of Year])
)```

This assumes there is a filter coming from the DimCalendar Table and the YearWeek column

Here's the pbix:

https://1drv.ms/u/s!Amqd8ArUSwDSz3y0sc4zWWT9zasB

2 REPLIES 2
New Contributor

## Re: Calculate with String xpression

See the attached PBIX.  But an overview:

1. Create a Date table ( please look at Power Query, there's a small function that does that)
2. Need to add YearWeek, Year, and Month columns
3. Load both tables, and relate to each other

Then the following measures:

```Current Week Products =
CALCULATE(
CONCATENATEX( FactTable, FactTable[Product],UNICHAR(10),
ALLSELECTED (DimCalendar[YearWeek])
)
)

Previous Week Products =
CALCULATE(
CONCATENATEX( FactTable, FactTable[Product],UNICHAR(10)),
FILTER (
ALL ( DimCalendar),
MAX(DimCalendar[Year] ) -1 = DimCalendar[Year]
&& Max( DimCalendar[Week of Year]) = DimCalendar[Week of Year])
)```

This assumes there is a filter coming from the DimCalendar Table and the YearWeek column

Here's the pbix:

https://1drv.ms/u/s!Amqd8ArUSwDSz3y0sc4zWWT9zasB

Frequent Visitor

## Re: Calculate with String xpression

Thanks a lot for the solution.