Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |