Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Formula Help

I have this table:

1.PNG
 I am attempting to put together a formula that looks at the latest month for a record and calculates the sum of revenue for that month and multiply it by 12. So in this case, the latest month recorded for revenue is December 2017 which would total 71.28 (271.28-200.00) and multiplying that by 12 would give 855.36 as the projected sum going forward. How can I set this up with the assumption that going forward in each month, there will be new revenue dates and amounts when refreshed? Thanks!

 

8 REPLIES 8
Anonymous
Not applicable

So there is no confusion beforehand, here is a look at my setup:

1.PNG
1.PNG

 

Account_c from Delta Account and Related_Account_c from Client Revenue were appended into a single table as the unique ID so I can work with fields from both tables. Cross filters on both tables are set to 'Both'. If I do not set these to 'Both', I don't get individual revenue for each account like I'm looking for:

Wrong (single cross filter):

1.PNG

 

Correct (Both cross filter):

1.PNG

Now, to the formula. I am looking to build a formula based upon the revenue date that looks back two months from the current month, gathers the sum of revenue for that month (based on Revenue_Date_c) and multiplies that number by 12. So in plain terms since it is March, it would be:

(Sum of January Revenue) *12

 

Once it is April it will then be:

(Sum of February Revenue) *12

 

etc. etc.

 

So looking at one company for example:

1.PNG

(Working with December revenue until database is updated to bring in January numbers) The sum of revenue for December would be 71.28 (271.28-200.00) and multiplying that by 12 is 855.36 which is the result I'm looking for. How can I create a formula that gives this?

 

 

 

 

Well, you can get your EndDate for your calculation like this:

 

EndDate = EOMONTH(EDATE(TODAY(),-2),0)

And your StartDate like this:

 

StartDate = EOMONTH(EDATE([EndDate],-1),0)+1

So, you could use these as your filters to calculate 2 months ago from whatever TODAY is and then multiply by 12.

 

So, what is going on is that you are going back 2 months from today and then getting the end of the month for that month (EndDate). StartDate takes that date, subtracts a month, finds the end of that month and then adds 1 day to give you the start of the month of EndMonth. Lots of different ways of calculating this but this method ensures that you can span years without a problem.

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try this MEASURE

 

Measure =
VAR lastmonth =
    MONTH ( LASTDATE ( Table1[Revenue_Date__c] ) )
VAR lastyear =
    YEAR ( LASTDATE ( Table1[Revenue_Date__c] ) )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue_Amount__c] ),
        YEAR ( Table1[Revenue_Date__c] ) = lastyear
            && MONTH ( Table1[Revenue_Date__c] ) = lastmonth
    )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@Zubair_Muhammad Not quite. It just matched the grand total of Revenue Column:

1.PNG

Hi @Anonymous,

 

@Zubair_Muhammad's solution should work for you.

 

Then based on your screenshot, it seems like you are creating a calculated column not a measure. Right? Please refer to following sample, and you can find out the difference between calculated column and measure. 

 

By the way, they are using the same expression:

 

11.PNG

 

Thanks,
Xi Jin.

Anonymous
Not applicable

@v-xjiin-msft not sure what I'm doing wrong:

1.PNG

@Anonymous

 

Could you share your file?


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@Zubair_Muhammad Unfortunately I don't think I can since my datasets contains company credentials to our CRM. What could be the issue is how the relationships are set up? Data from two different datasets are connected via a unique ID table and the security filter on both tables is set to 'Both'. The data I'm showing comes from two different places connected by the ID's highlighted:


1.PNG


Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.