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 |
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.
Can you please provide some sample pbix/excel file to work with. It should be a simple calculation based on date table. Once we get some sample file, I can look into this tomorrow or someone else from this amazing group can provide you required solution.
Thanks for the response. The new measure logic is based on a standard calendar although I hope to eventually apply it to a fiscal calendar down the road; noting I see several good sources to do that. I’m uploading a sample Excel source file and PBIX.
Thanks for sending your pbix/excel file. It was real help indeed to provide solution. I am going to give you some idea about how it will work and you just need to put formula’s as per your requirement. Things I have done.
Added Date table and created relationship with Invoice Table.
Created base measures( Total Amount, Total Amount(Monthly), Total Amount(Yearly)
Created calculated column to bring MonthNo in [Invoice Table].
Created required measure. You can adjust formula based on your requirement and it should work perfectly fine. I have test it for PID 276579. Its April Month invoice amount was $80 and for 4 months, it came as 320.
Current Year Revenue(New) =
VAR Monthly_Revenue =
SUMX ( 'Invoice Table', 'Invoice Table'[MonthNo] * [Total Amount(Monthly)] )
VAR Yearly_Revenue =
SUMX (
'Invoice Table',
[Total Amount(Yearly)] / 12 * ( 12 - 'Invoice Table'[MonthNo] )
)
RETURN
Monthly_Revenue + Yearly_Revenue
Thank you!! I was able to make changes to some the new measure formulas to produce the desired business logic. I needed your practical approach to do that, and am uploading the final PBIX accordingly. Enterprise DNA - DAX Calc No of Months.pbix (114.6 KB)
Your file is looking good and seems that you are able to create your desired results. Please specify if something is not working, so I can help out in this. Like, what output do you require and what’s wrong in current file?
We have successfully completed the original question. The new measures properly handle use of Year filter/slicer. Thank you!! However, I do have a second or follow-up topic. I need one more new measure to reflect the invoices which “Carry over” from the previous year into the current year.
The new measure is “Carry Over from Previous Year”. In the context of the Year=2020 slicer, the new Carry Over measure would use all of the Invoices from 2019 and essentially perform the same logic (calculation) of the “Next Year Revenue (Final)” measure. In DAX, how do control/change the active records from 2020 to 2019 for this particular measure? Or, asked a bit differently, is there a way I can use a 2020 Year slicer value to end up with a dataset from both 2019 and 2020 to produce each of these measures?
2020 Carry Over from 2019 invoices (to be determined)
2020 Current Year Revenue from 2020 invoices (already done)
2020 Next Year Revenue (i.e. 2021) from 2020 invoices (already done)
Or is a Year filter/slicer not practical here, and I need to specifically create measures for each year throughout all invoice dates?
In future please always start a new forum topic with an appropriate title - so others can find it more easily if and when they encounter a similair problem. Thank you!
I marked your Dates table as Date table and then replaced the Invoice date with the Dates date in your table visual. Applied the same logic as the “Next Year Revenue (Final)” by just wrapping it inside CALCULATE and adding SAMEPERIODLASTYEAR, with this result.
Thank you. Very helpful, and I will heed your new topic advice next time.
The complexity with the three new measures generated is that the first two - Current Year Revenue(Final) and Next Year Revenue(Final) are wholly impacted by filter/slicer context. I added a month Slicer to the PBIX. However, an alternate version of the Carry Over Revenue measure is to be derived based on the previous year as a whole regardless of any month filter/slicer. Thus, to help my understanding, I’d like to add two additional “Carry-Over” measures:
whether 2020 - January is sliced or 2020 - July is sliced, they both result in all amounts from 2019 which carry forward into any 2020 date. $27,268.18 is the proper Carry-Over Revenue value regardless of the selected Month, so it’s really “Carry-Over Revenue Full Year”… fixed/static across the year,
Carry-Over Revenue Previous 12 Months: With a month slicer value selected, produce the records from the previous twelve months which would be carried over into the selected month. For example, with 2020-October selected, sum all of the calculated monthly and yearly measures since 2019-October; noting that by month is really from 1-Nov-2019 forward and by date (ex. 15-Oct-2020) would be from 16-Oct-2019 forward.
2a) It would also seem necessary to be able to distinguish/report the “Carry-Over Revenue Previous 12 Months” generated by each year (2019 and 2020 in this case).
Hopefully, this descriptive logic makes sense, and I can learn how you handle segmenting this data via DAX in producing these measures.
Glad that you are making progress. I would strongly suggest to please do following for your posts. These will be inline with forum rules and will also help you to get your answer quickly.
Please ask one question/post if you are stuck on something. Also, it would be helpful if you provide subject according to your question. Otherwise, anyone looking to get idea/solution in the future will be lost in so many details.
Provide sample file as you have provided. As, otherwise it will be a guess work.
Provide some example from your data which should be your desired outcome. Means, it should state, what’s currently happening and what do you want to get.
You can achieve this by choosing edit interactions on the ribbon (when you have the month slicer selected) and set the interaction to non for the Card visual. Now the total will be shown no matter what month name is selected.
.
.
Please review the DATEADD, I’m confident this will help you resolve it.
I’m onboard with Edit Interactions controlling the [card] visualization. However, I do need the Carry Over Revenue measure itself to have different variations and be reported as a table visualization column along with Current Year Revenue and Next Year Revenue which should be impacted by a Month slicer.
Admittedly, I’m not seeing how DATEADD would be used with CALCULATE to produce a virtual table of the previous 12 months from the selected months within the formula/logic of a Carry Over Revenue Previous 12 Months measure.
Quick summary of 2020 - February slicers are selected (separate slicers simply to help validate):
Current Year Revenue: All records with Invoice dates in 2020-Feb summed to reflect the total revenue generated in 2020 based on the Invoice Type.
Next Year Revenue: All records with Invoice dates in 2020-Feb summed to reflect the total revenue generated in 2021 based on the Invoice Type.
Carry-Over Revenue Previous 12 Months: All records in the 12 months prior to 2020-Feb (i.e. 2019-Mar to 2020-Jan) which produce revenue in 2020-Feb (Invoice Type = ‘Monthly’ or ‘Yearly’).
I greatly appreciate the help given already, and I’m fine posting a new topic and re-describing the PBIX/measures if you think that is appropriate at this point.
