cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Tricky DAX Formula

I have this table:

1.PNG

 

I am looking to build a formula that will look back 2 months, gather the sum of revenue for that month, and multiply it by 12 to get my Projected Sum. So hypothetically, the formula for March would look like:

(Sum of January Revenue) * 12

 

Once April is here, it would then be

(Sum of February Revenue) * 12

 

etc. etc.

 

This way if the formula looks back and returns no data, it tells us that specific company has recently been terminated since there was no projected sum reported.

 

Is there a way to do something like that? Thanks!

6 REPLIES 6
Highlighted
Super User III
Super User III

Hi  @Anonymous,

 

You should use something like this:

 

Projected Sum =
CALCULATE (
    TOTALYTD (
        SUM ( Table[Revenue_Amount_c] );
        DATEADD ( Table[Revenue_Date_C]; -2; MONTH )
    )
)
    * 12

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Anonymous
Not applicable

@MFelix This displays when trying to add it to a table with other data:

1.PNG

Highlighted

Do you have a calendar table that relates with revenue date?

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Anonymous
Not applicable

@MFelix I have a Date table but I don't have a relationship setup with it. Typically when I build formulas I just reference my date table in the formula if needed:

1.PNG

 

Highlighted
Anonymous
Not applicable

@MFelix I've substitued the Revenue Date for the Date in my calendar table and now I no longer get the error:

1.PNG

 

Only problem is now it is summing each individual line. I was hoping to get the sum for December (71.28) and multiply that by 12 (855.36) as the answer I was looking for. I don't need totals for older dates, just the latest month. Each month there will be new revenue and revenue dates and I will need to take the total for the new month and multiply it by 12.

Highlighted

Hi @Anonymous,

 

Do you want to dispaly the information as you have in your print screen?

 

Measures are calculated based on context so when you add the full date field, it will make the sum for each individual row and not for the aggregated month. If instead of date you add in Month/year this will give you the sum per month since it will be getting the information for all the dates within the month.

 

If you have the need to show individual dates do you want t have single day information or cumulative?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors