Tricky DAX Formula

Question

I have this table:&nbsp;I am looking to build a formula that will look back 2 months, gather the sum of revenue&nbsp;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&nbsp;Once April is here, it would then be(Sum of February Revenue) * 12&nbsp;etc. etc.&nbsp;This way if the formula looks back and returns no data,&nbsp;it tells us that specific company has recently been terminated since there was no&nbsp;projected sum&nbsp;reported.&nbsp;Is there a way to do something like that? Thanks!

mfelix · Answer

Hi&nbsp;&nbsp;Anonymous,
&nbsp;
You should use something like this:
&nbsp;
Projected Sum =
CALCULATE (
    TOTALYTD (
        SUM ( Table[Revenue_Amount_c] );
        DATEADD ( Table[Revenue_Date_C]; -2; MONTH )
    )
)
    * 12
&nbsp;
Regards,
MFelix

anonymous · Answer

MFelix&nbsp;This displays when trying to add it to a table with other data:

mfelix · Answer

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

anonymous · Answer

MFelix&nbsp;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:&nbsp;

anonymous · Answer

MFelix&nbsp;I've substitued the Revenue Date for the Date in my calendar table and now I no longer get the error:&nbsp;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.

Forum Discussion

Tricky DAX Formula

6 Replies

Related content

DAX Formula Help

DAX Formula

DAX Formula

Tricky IF DAX Formula

DAX Formula