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

## Tricky DAX Formula

I have this table:

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

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:

Highlighted
Super User III
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:

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:

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
Super User III

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

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors