cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkaelin Member
Member

Rolling 12 month Sum for completed months

Good morning,

 

Looking for a rolling 12 month and/or a rolling 365 days of sales, however, if the month is not complete then default to previous months rolling 12 month value.  I've tried several methods on this forum, but nothing seems to be producing the desired result.  Not sure if anyone has any tips to help solve this.  Thank you!!!

 

Rolling12M.jpgDesired result in yellow

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Rolling 12 month Sum for completed months

@Jkaelin,

Create the following measures and check if you get expected result.

last month of last year = CALCULATE([Rolling 12M Sales],FILTER(ALL('Master_Calendar'),'Master_Calendar'[Year]=YEAR(TODAY())-1 && 'Master_Calendar'[MonthOfYear]=12))
Expected result = IF(YEAR(TODAY())>MAX('Master_Calendar'[Year])&&TODAY()>EOMONTH(MAX('Master_Calendar'[Date]),0),[Rolling 12M Sales],[last month of last year])

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: Rolling 12 month Sum for completed months

Can you post your formula? I'm guessing it can be fixed with an IF statement and a CALCULATE with a MAX filter but can't know for sure.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Jkaelin Member
Member

Re: Rolling 12 month Sum for completed months

@Greg_Deckler

 

Hey Smoupre! This is one of my formula's.  I've tried a variety of IF, THEN statements to no avail.  

 

 

Rolling 12M Sales :=
CALCULATE (
    [Total Revenue],
    DATESBETWEEN (
        'Master_Calendar'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Master_Calendar'[Date] ) ) ),
        LASTDATE ( 'Master_Calendar'[Date] )
    )

 

This formula was posted by @Vvelarde in another related thread, but it would error out on me.  

 

ValueCalc = if(TODAY()>=EOMONTH(VALUES(Table1[DateRef]);0);EXPRTRUE;EXPRFALSE)

 

This seems like it should be easy to do but then I can't do it, makes me feel like I have no understanding of Row/Filter context.  Smiley Sad

 
Super User
Super User

Re: Rolling 12 month Sum for completed months

So, is the main table you are dealing with 'Master_Calendar' or some other table? And if some other table, what is filtering dates from master_calendar, a relationship?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Jkaelin Member
Member

Re: Rolling 12 month Sum for completed months

@Greg_Deckler

 

The main table is my factSales table.  My main calendar table is 'Master_Calendar'.  Master Calendar is a one-to-many to the factSales table.  Any ideas on a good approach?  

 

Kindly - James

Moderator v-yuezhe-msft
Moderator

Re: Rolling 12 month Sum for completed months

@Jkaelin,

Create the following measures and check if you get expected result.

last month of last year = CALCULATE([Rolling 12M Sales],FILTER(ALL('Master_Calendar'),'Master_Calendar'[Year]=YEAR(TODAY())-1 && 'Master_Calendar'[MonthOfYear]=12))
Expected result = IF(YEAR(TODAY())>MAX('Master_Calendar'[Year])&&TODAY()>EOMONTH(MAX('Master_Calendar'[Date]),0),[Rolling 12M Sales],[last month of last year])

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jkaelin Member
Member

Re: Rolling 12 month Sum for completed months

@v-yuezhe-msft

 

Thank you for your help.  Sorry for delay in responding, I been under the weather.  The measures you created work and appears to do the trick.  Thank you!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 163 members 1,965 guests
Please welcome our newest community members: