custom period for calculate sum

[Anonymous]

Hey guys,

How to create a period to calculate a sum of a value between the 19th of the previous month and the 20th of the current month. I would like to be able to calculate my sum over this period and not from 1 to 31? I have this calendar for my time intelligence 

Thank all ! 

[ChrisMendoza · Accepted solution]

@Anonymous -

Take a look at the attachment. I attempted a 'running total' measure then decided you'd probably want to use similar logic for a custom period so I added that as well. Hope it helps 😎!

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hello @CharlyExperteam ,

According to my understanding, you want to calculate the sum in a specific date frame (19o-20), right?

You can add a MonthDay column to the Date table using the following formula:

MonthDay =
MONTH ( [Date] ) * 100
    + DAY ( [Date] )

And then use the column directly to limit the range:

19th-20th =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[MonthDay]
            >= ( MAX ( 'Table'[Month] ) - 1 ) * 100 + 19
            && 'Table'[MonthDay]
                <= MAX ( 'Table'[Month] ) * 100 + 20
    )
)

My final output looks like this:

Here's the pbix file.

Have I answered your question? Please mark my answer as a solution. Thanks a lot.
If not, load some insensitive data samples and expected output.

Best regards
Eyelyn Qin

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understand , you want to calculate the sum in a specific Date frame(19th-20th), right?

You could add a MonthDay column in Date table using the following formula:

MonthDay =
MONTH ( [Date] ) * 100
    + DAY ( [Date] )

 And then use the column directly to limit the range:

19th-20th =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[MonthDay]
            >= ( MAX ( 'Table'[Month] ) - 1 ) * 100 + 19
            && 'Table'[MonthDay]
                <= MAX ( 'Table'[Month] ) * 100 + 20
    )
)

My final output looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[ChrisMendoza · Accepted solution]

@Anonymous -

Take a look at the attachment. I attempted a 'running total' measure then decided you'd probably want to use similar logic for a custom period so I added that as well. Hope it helps 😎!