Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
@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
@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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |