The attached PBIX file reflects a series of contract service invoices with invoice dates in calendar 2019 and 2020. The reporting is seen as being monthly… with standard calendar summaries at quarter and year levels. Based on the Invoice Type, there are two measures producing desired results:
- Current Year Revenue, where the invoice transactions for the selected Year-Month are the basis for determining the revenue associated to that month and the remainder of the selected year.
- Next Year Revenue, where the invoice transactions for the selected Year-Month are the basis for determining the revenue associated to the next calendar year. FYI, invoices with dates in 2020-Feb generate revenue in 2021-Jan (e.g. 12 months of service).
However, a 3rd key measure, Carry-Over from Previous Year, only works as desired when disabling the Interaction between the MonthName slicer and the card visualization. In simplest terms, Carry-Over is the revenue generated from invoices on dates prior to the selected period of time where Invoice Type = ‘Monthly’ or ‘Yearly’. Monthly and Yearly invoices generate revenue in their current month (Invoice Date) and the following 11 months for total service of 12 months.
However, there are a couple of variations needed of a Carry-Over measure as follows:
Carry Over for the Entire Year: No matter which month is selected, every Monthly or Yearly invoice from the previous year (really, from Feb 1 - Dec 31 of previous year). Whether the month selected is 2020-Jan or 2020-Jul, the same value from invoices dated 2/1/19 - 12/31/19 would result based on selected Year=2020. This is why the Card visualization is accurate using Edit Interactions while the table visualization column is not accurate since both Year & Month filters need to be active for the other measures.
Carry Over for the Previous 12 (actually, 11) Months: Here is where the selected Month rightfully impacts results. If the selected Month is 2020-Feb, then this Carry Over measure variation is based on Monthly and Yearly invoices dated 2019-Mar (i.e. Mar 1, 2019) through 2020-Jan (i.e. Jan 31, 2020). note: I presume Year-Month addresses any leap day nuance.
I’m looking for the proper DAX for the two variations of Carry-Over measures described where I don’t have to rely on Edit Interactions.
Thank you in advance for the support.
Enterprise DNA - Measure Variations.pbix (116.6 KB)