I am looking to create two measures in DAX reflecting “Current Year Revenue” and “Next Year Revenue” from the following invoice transaction able columns:
| Invoice Date | Invoice Type | Amount | Current Year Revenue | Next Year Revenue |
| 03/15/2020 | Monthly | $40.00 | $400.00 | $80.00 |
| 05/29/2020 | Yearly | $300.00 | $200.00 | $100.00 |
Both invoices represent a year of service allocated to revenue by month, and monthly date math is the basis for each measure to calculate per the following logic:
Where Invoice Type = ‘Monthly’…
Current Year Revenue = # of months in 2020 from Invoice date * Amount
Next Year Revenue = (12 - # of remaining months in 2020 from invoice date) * Amount
Where Invoice Type = ‘Yearly’…
Current Year Revenue = Amount / 12 * (# of months in 2020 from Invoice date)
Next Year Revenue = Amount / 12 * (12 - # of months in 2020 from Invoice date)
Or… Next Year Revenue = Amount – Current Year Revenue
Note: # of days is not a factor; only # of months is calculated where the current month is always included in Current Year Revenue.
I’ve been unable to recognize the DAX required to calculate these two measures, and would greatly appreciate your guidance.