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.
I'm making a table where i want to measure a few things against a chosen dimension.
Now the below mockup can be partially done with a matrix but without the last 2 columns.
For a table I would need to know how to create a measure that has is segmented towards Month-1, Month -2 Month-3 etc for each of the column headers. then the last last column would be simply (Month-1/Month-13)-1 or whatever.
Whats the formulas functions to achieve this? The quick measure forumula only works when your y dimension is a date.
Hi @StephenF ,
For your scenario, you can create variable to store current column label, then convert it to date and calculate out previous date.
After these steps, you can simply extract year and month part as filter conditions for calculate:
Measure = VAR currDate = MAX ( Table[YearMonth] ) VAR prevDate = DATE ( LEFT ( currDate, 4 ), RIGHT ( currDate, 2 ) - 1, 1 ) RETURN CALCULATE ( SUM ( Table[Amount] ), FILTER ( ALLSELECTED ( Table ), [YearMonth] = YEAR ( prevDate ) * 100 + MONTH ( prevDate ) ), VALUES ( Table[Type] ) )
If you still confused on coding formula, can you please share some sample data for test?
Regards,
Xiaoxin Sheng
Thanks, This approach fails in the case where there is no value in a month for a particular dimension.
Say the Dimension Blue has no value for Last month but does have a value for the month before that. It will then get the value for currentmonth-3.
So I guess a test needs to be done to see if a value is available and if not return zero also or,
HI @StephenF ,
According to your description, it sounds like your date dimension not continuous. If this is a case, I think you can modify 'previous date' variable to find out last date based on current date.
Measure = VAR currDate = MAX ( Table[YearMonth] ) VAR temp = CALCULATE ( MAX ( Table[YearMonth] ), FILTER ( ALLSELECTED ( Table ), [YearMonth] < currDate ) ) VAR prevDate = DATE ( LEFT ( temp, 4 ), RIGHT ( temp, 2 ), 1 ) RETURN CALCULATE ( SUM ( Table[Amount] ), FILTER ( ALLSELECTED ( Table ), [YearMonth] = YEAR ( prevDate ) * 100 + MONTH ( prevDate ) ), VALUES ( Table[Type] ) )
Regards,
Xiaoxin Sheng
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |