Problem to allign 'Time intelligence' measures with text fields

[didi42]

Hello,

I am struggling with a problem, which seems very basic but for which I cannot find any solution and would really appreciate your help.

• I have a simple data set of sales of product per store and per dates, as well as text comment for each product sale

•  This set is linked to a calendar table created with CALENDAR() function

CALENDAR(Date(2021,01,01),Date(2021,12,31))

• I added one measure to return the previous mont sales and one to return the variation between 2 months

Previousmonth Product1 = CALCULATE(SUM(Sheet1[Product 1]),PREVIOUSMONTH('Calendar'[Date].[Date]))
Previousmonth variation product1 = SUM(Sheet1[Product 1])-[Previousmonth Product1]

The measures are working properly but I am facing problems when I want to display them in a table alligned with the text comment column of the data set. I can display correctly a table with the current sales and variation for each store and another table with the current sales and comments for each store but I am not able to have everything shown properly in a single table (Current and previous month do not allign anymore, variation is not calculated in one row, and comments of previous month are also shown, irrespective of the date filter applied...)

Would you have an idea of what I am doing wrong ?

Thanks in advance for your help.

[Anonymous]

Hi, 

You can try this 

1. Create a measure for the total of Product 1,   Total_Product_1 = SUM(your_table[Product 1])
2. Create 2nd measure for the previous month Product 1, Previous_month_Total_Product_1= 

    Calculate([Total_Product_1],DATEADD('CALENDAR'[Date],-1,MONTH))

3. Create Variation Measure  Variation = [Previous_month_Total_Product_1]-[Total_Product_1]

4. Use calander slicer for changing month

[didi42 · Accepted solution]

Hello,

Thanks for taking the time to answer me. I tried to modify the measures with the one you provided me, however, I'm still facing the same issue.

My basic understanding would be that, since with my measures "call" values from past month, then the text fields of previous month are also displayed.

EDIT :I found a small trick which seems to work : Make a measure from my text.

Text_as_measure = Calculate(MAX(Sheet1[Product 1 comment]))

This way ,measures are shown correctly in one row and then everything is alligned

Is it a correct way to do it, or could I improve it ?