cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sushil_Kumar777 Frequent Visitor
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 ProductItemID
201942Apple1
201942Orange2
201942Banana3
201842Avocado4
201842Bilberry5
201842Blackberry6

 

Expected Output:

Week Slicer201942
  
Current Week ProductsCurretn Week LY Products
AppleAvocado
OrangeBilberry
BananaBlackberry
1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
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

Final Output.png

 

Here's the pbix:

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

2 REPLIES 2
Nick_M New Contributor
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

Final Output.png

 

Here's the pbix:

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

Sushil_Kumar777 Frequent Visitor
Frequent Visitor

Re: Calculate with String xpression

Thanks a lot for the solution.