cancel
Showing results for
Did you mean:

## include only same values that are in previous month

Hello,

Im strugling with calculation that should include only same values that are in previous month.

I need to calculate % change of price over months, but I have to include only values that are filled in previous month (if I include all the values, result is distorted).

Example:

for 2020/04 only type B and C is included into current price (type D was not in previous month)
previous price is including only B and C as well (type A is not in current month)

% change will be 110/90 = 20%

 type month price A 2020/01 100 B 2020/01 50 C 2020/01 50 D 2020/01 10 A 2020/02 110 B 2020/02 50 C 2020/02 50 D 2020/02 10 A 2020/03 120 B 2020/03 30 C 2020/03 50 B 2020/04 50 C 2020/04 100 D 2020/04 10

so far I've tried "allselected" and "values", but no good result.

Thank you for any help in advance.

Zuzana

1 ACCEPTED SOLUTION
Community Support

Hi @Zuzana ,

Try this:

1. Create a "StartDayofMonth" column in your Fact table.

``````StartDayofMonth =
CONVERT ( [month] & "/1", DATETIME )
``````

2. Create another Dates table.

``````Dates =
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 4, 30 ) ),
"YearMonth", FORMAT ( [Date], "YYYY/MM" )
)
``````

3. Create another Types table.

``````Types =
DISTINCT ( 'Fact Table'[type] )
``````

4. Create relationships.

5. Create measures.

``````ModifiedCurrentMonthPrice =
VAR CurrentMonthPrice =
SUM ( 'Fact Table'[price] )
VAR PreviousMonthPrice =
CALCULATE ( SUM ( 'Fact Table'[price] ), PREVIOUSMONTH ( Dates[Date] ) )
VAR ModifiedCurrentMonthPrice =
IF ( PreviousMonthPrice = BLANK (), BLANK (), CurrentMonthPrice )
RETURN
ModifiedCurrentMonthPrice
``````
``````ModifiedPreviousMonthPrice =
VAR CurrentMonthPrice =
SUM ( 'Fact Table'[price] )
VAR PreviousMonthPrice =
CALCULATE ( SUM ( 'Fact Table'[price] ), PREVIOUSMONTH ( Dates[Date] ) )
VAR ModifiedPreviousMonthPrice =
IF ( CurrentMonthPrice = BLANK (), BLANK (), PreviousMonthPrice )
RETURN
ModifiedPreviousMonthPrice
``````
``````% MoM change =
VAR SumCurrent =
SUMX ( Types, [ModifiedCurrentMonthPrice] )
VAR SumPrevious =
SUMX ( Types, [ModifiedPreviousMonthPrice] )
RETURN
DIVIDE ( SumCurrent - SumPrevious, SumPrevious )
``````

BTW, .pbix file attached.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

4 REPLIES 4
Community Support

Hi @Zuzana ,

Try this:

1. Create a "StartDayofMonth" column in your Fact table.

``````StartDayofMonth =
CONVERT ( [month] & "/1", DATETIME )
``````

2. Create another Dates table.

``````Dates =
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 4, 30 ) ),
"YearMonth", FORMAT ( [Date], "YYYY/MM" )
)
``````

3. Create another Types table.

``````Types =
DISTINCT ( 'Fact Table'[type] )
``````

4. Create relationships.

5. Create measures.

``````ModifiedCurrentMonthPrice =
VAR CurrentMonthPrice =
SUM ( 'Fact Table'[price] )
VAR PreviousMonthPrice =
CALCULATE ( SUM ( 'Fact Table'[price] ), PREVIOUSMONTH ( Dates[Date] ) )
VAR ModifiedCurrentMonthPrice =
IF ( PreviousMonthPrice = BLANK (), BLANK (), CurrentMonthPrice )
RETURN
ModifiedCurrentMonthPrice
``````
``````ModifiedPreviousMonthPrice =
VAR CurrentMonthPrice =
SUM ( 'Fact Table'[price] )
VAR PreviousMonthPrice =
CALCULATE ( SUM ( 'Fact Table'[price] ), PREVIOUSMONTH ( Dates[Date] ) )
VAR ModifiedPreviousMonthPrice =
IF ( CurrentMonthPrice = BLANK (), BLANK (), PreviousMonthPrice )
RETURN
ModifiedPreviousMonthPrice
``````
``````% MoM change =
VAR SumCurrent =
SUMX ( Types, [ModifiedCurrentMonthPrice] )
VAR SumPrevious =
SUMX ( Types, [ModifiedPreviousMonthPrice] )
RETURN
DIVIDE ( SumCurrent - SumPrevious, SumPrevious )
``````

BTW, .pbix file attached.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

Super User IV

@Zuzana , That is why I added this one

MTD price = sumx(values(Table[Type]) , if(isblank([last MTD price]) , blank(),[MTD price 1]))

id last month is blank , take this month also as blank

Proud to be a Super User!

Super User IV

@Zuzana , Try with a date table and time intelligence

MTD price 1 = CALCULATE(max(Table[price]),DATESMTD('Date'[Date]))

MTD price = sumx(values(Table[Type]) , if(isblank([last MTD price]) , blank(),[MTD price 1]))

diff = [MTD price] -[last MTD price]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Proud to be a Super User!

Hello,

thank you for your response, I dont have problem to calculate previous period value.

Im having issue with including only selected types (as is described in example)

Zuzana

Announcements

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors