Forum Discussion
Calculate measure based on month filter from visualization
This solution has not worked for me, even using the MAX in the measure.
Solution SageHey Anonymous,
Create a calendar table where each row represents a date, with a month column that a user can select. You can do this in DAX, for example:
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
"Month",
FORMAT ( [Date], "mmm yyyy" )
)Create a relationship between the calendar table and your fact table.
If the user has selected one month, you can easily calculate the number of days in that month by counting the number of rows in the calendar table:
No of days =
COUNTROWS ( 'Calendar' )I already have the Number of Months calculated column in my Dates table (this is not the measure I need) and I have a relationship between my fact table and the Dates table.
I need the below measure to use the month filter from my visualization to come up with the no of days in the month. The measure works if I just put in the number of days for the specific month I have filtered to, but I want the measure to be dynamic.
units x Dates[daysinmonth] x 24 (hours in a day)
- themistoklis
Community Champion
Anonymous
To find the number of days in a selected month use the following formula in a new column:
No_of_Days= DAY(EOMONTH(Table[Date], 0))
So the final formula will be:
units x No_of_Days x 24
No, does not work.
v-jingzhangCommunity Support
Hi Anonymous
You can try below measure for [No_of_Days].
No_of_Days = DAY ( EOMONTH ( MAX ( 'Date'[Date] ), 0 ) )
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
