Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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%

 

 

 

 

 

 

 

 

 

 

 

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

 

  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    Try this:

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

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

     

    2. Create another Dates table.

    Dates =
ADDCOLUMNS (
    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

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Try this:

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

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

     

    2. Create another Dates table.

    Dates =
ADDCOLUMNS (
    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.

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , 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