Calculate with String xpression

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

Thanks a lot for the solution.