cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

custom period for calculate sum

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 

 

help.PNGhelp2.PNG

 

 

Thank all ! 

2 ACCEPTED SOLUTIONS
Super User I
Super User I

@CharlyExperteam -

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 😎!

 

CharlyExperteam-02.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

Microsoft
Microsoft

Hi @CharlyExperteam ,

 

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:

11.13.1.1.PNG

 

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

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

Hi @CharlyExperteam ,

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

Microsoft
Microsoft

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:

11.13.1.1.PNG

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

Microsoft
Microsoft

Hi @CharlyExperteam ,

 

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:

11.13.1.1.PNG

 

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

View solution in original post

Super User I
Super User I

@CharlyExperteam -

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 😎!

 

CharlyExperteam-02.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors