Forum Discussion
Average at month 5
Hi,
I am looking for some help, and I am going round and around in circles. I want to get the figure a table row is at month 5. So I have a calendar table and a snapshot date. I want to see the created month % at snapshot month 5 and either have it as a seperate measure and then add condisytional formating on these specific cells. I have tried to offset to give me month 5 in my date table but it doesnt seem to be working, and the issue is I only have power bi on browser currently so I cant work out what the actual measure is doing. This is the table so for January 2024, I would like to see the figure of 22.65% in May snapshot highlighted or just be able to get it onto a separate table. I know that this is possible and I am sure I have done it before but I am going slowly insane trying to get it to work. Any help would be amazing.
Thanks for the reply from rajendraongole1, please allow me to provide another insight.
Hi AnnaMa ,
I am not sure how your semantic model is designed, here is my sample.The semantic model is as follows.
Use the following measure to mark the fifth month's value.color = VAR row_month = MONTH(MAX('Calendar'[Date])) VAR column_month = MONTH(MAX('Table'[Date])) RETURN IF(column_month-row_month=4,"Red")
Use this measure to set the conditional format of the matrix values. The final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- rajendraongole1
Super User
Hi AnnaMa - You need to create a DAX measure that dynamically retrieves the value for the 5th snapshot month.
eg : Month5Value =
VAR Month5Date =
CALCULATE(
MIN('Calendar'[Date]),
FILTER(
'Calendar',
'Calendar'[MonthOffset] = 5
)
)
RETURN
CALCULATE(
SUM(Table[CreatedMonthPercentage]),
'Calendar'[Date] = Month5Date
)you can replace the table name as per your model .MonthOffset should be a column that calculates the difference between the current month and the snapshot base month.
To create a separate table that displays the values for Month 5:
Use the Month5Value measure in a new table visual.You can add the "Year" or any other context column to ensure clarity.
Thanks for the reply from rajendraongole1, please allow me to provide another insight.
Hi AnnaMa ,
I am not sure how your semantic model is designed, here is my sample.The semantic model is as follows.
Use the following measure to mark the fifth month's value.color = VAR row_month = MONTH(MAX('Calendar'[Date])) VAR column_month = MONTH(MAX('Table'[Date])) RETURN IF(column_month-row_month=4,"Red")
Use this measure to set the conditional format of the matrix values. The final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
