cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zuzana
Advocate II
Advocate II

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%

image_2020-11-30_104038.png

 

 

 

 

 

 

 

 

 

 

 

typemonthprice
A2020/01100
B2020/0150
C2020/0150
D2020/0110
A2020/02110
B2020/0250
C2020/0250
D2020/0210
A2020/03120
B2020/0330
C2020/0350
B2020/0450
C2020/04100
D2020/0410

 

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

 

Thank you for any help in advance.

Zuzana

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Zuzana ,

 

Try this:

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

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

startdayofmonth.JPG

 

2. Create another Dates table.

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

datestable.JPG

 

3. Create another Types table.

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

typestable.JPG

 

4. Create relationships.

relationships.JPG

 

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 )

percent.JPG

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.

 

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @Zuzana ,

 

Try this:

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

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

startdayofmonth.JPG

 

2. Create another Dates table.

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

datestable.JPG

 

3. Create another Types table.

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

typestable.JPG

 

4. Create relationships.

relationships.JPG

 

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 )

percent.JPG

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.

 

View solution in original post

amitchandak
Super User IV
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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

@Zuzana , Try with a date table and time intelligence

 

MTD price 1 = CALCULATE(max(Table[price]),DATESMTD('Date'[Date]))
last MTD price = CALCULATE(max(Table[price]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

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