Creating a measure for # of days in a month

[jonnyA]

I'm trying to figure out how to take my Charges divided by # of days in the month or months selected.

I think if someone could tell me how to write a measure that displays the # of days in a month i could figure it out by taking the "Charges" divided by "# of days per month chosen"

For example:

If my "charges" were $10,000 in October then I would divide that by "31 days" (The total days in October)  then my answer is $322.58.

[v-yingjl · Accepted solution]

Hi @jonnyA ,

Seems like you do not have a calendar table, create a calculated table like this based on your [Month Year Sort]:

Calendar = CALENDAR("2017/9/1","2018/12/31")

Create a calculated column for it which is simliar with the [Month Year Sort]:

mthy =
(
    YEAR ( 'Calendar'[Date] ) * 100
        + MONTH ( 'Calendar'[Date] )
) & " - " & 'Calendar'[Date].[Month]

Create a measure to calculated the result:

Result = 
SUM ( 'MthYear_Sort'[Charges] )
    / CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[mthy] IN DISTINCT ( 'MthYear_Sort'[Month Year Sort] )
        )
    )

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonnyA]

@v-yingjl I have a MTHYear_Sort with my data, however, i do not know how to write the measure?  Can you help me out writing the measure?  I think I need to plug in MTHYear_Sort somewhere, but I do not know how to do it?  Appreciate the help!!!

[v-yingjl]

Hi @jonnyA ,

What does the [MTHYear_Sort] represent in your table? The date column, the month column or anything else?

Could you please consider sharing a simple sample data without any sesentive information abouth this issue for further discussion which could let us better to help you solve it.

Best Regards,
Community Support Team _ Yingjie Li

[jonnyA]

Here is an example of what the "MthYear_Sort" Table represents ...

Is this what you're looking for?

[Syndicate_Admin · Accepted solution]

Hi @jonnyA ,

Seems like you do not have a calendar table, create a calculated table like this based on your [Month Year Sort]:

Calendar = CALENDAR("2017/9/1","2018/12/31")

Create a calculated column for it which is simliar with the [Month Year Sort]:

mthy =
(
    YEAR ( 'Calendar'[Date] ) * 100
        + MONTH ( 'Calendar'[Date] )
) & " - " & 'Calendar'[Date].[Month]

Create a measure to calculated the result:

Result = 
SUM ( 'MthYear_Sort'[Charges] )
    / CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[mthy] IN DISTINCT ( 'MthYear_Sort'[Month Year Sort] )
        )
    )

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl · Accepted solution]

Hi @jonnyA ,

Seems like you do not have a calendar table, create a calculated table like this based on your [Month Year Sort]:

Calendar = CALENDAR("2017/9/1","2018/12/31")

Create a calculated column for it which is simliar with the [Month Year Sort]:

mthy =
(
    YEAR ( 'Calendar'[Date] ) * 100
        + MONTH ( 'Calendar'[Date] )
) & " - " & 'Calendar'[Date].[Month]

Create a measure to calculated the result:

Result = 
SUM ( 'MthYear_Sort'[Charges] )
    / CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[mthy] IN DISTINCT ( 'MthYear_Sort'[Month Year Sort] )
        )
    )

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl]

Hi @jonnyA ,

Based on your description, if you do not have a date table, you can create a calendar table like this:

Calendar = CALENDAR("2020/1/1","2020/12/31")

Create a measure like this:

Result = 
CALCULATE (
    SUM ( 'Table'[charges] ) / COUNT ( 'Calendar'[Date] ),
    FILTER (
        'Table',
        'Table'[Month] = SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

@jonnyA ,  @AlB is correct, a Date Table makes this a snap. I have a date table here - https://bit.ly/DateTableByEd - that will give you a pretty comprehensive date table. You can count rows if your dates are filtered properly, or use the [Days in Month] field in this date table.

[AlB]

Hi @jonnyA 

Assuming you have a date table ( create one otherwise):

Measure = COUNT( DateTable[Date] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers