Tricky DAX Formula

[Anonymous]

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!

[MFelix]

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

[Anonymous]

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

[MFelix]

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

[Anonymous]

@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.

[MFelix]

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

[Anonymous]

@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: