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.
Kevin
Enterprise DNA - Measure Variations.pbix (116.6 KB)
Thanks for splitting up the topic and providing more detail on your requirement.
BTW can you please mark a post within the previous topic as solution? You’ll find that option by going to the … (3 dots). That way other members will know it’s resolved.
I’ll get back to you later today.
Cheers
Hi @kkieger,
I extended your Date table by adding a MonthOffset and a YearOffset, like so:
InsertMonthOffset = Table.AddColumn(InsertQuarternYear, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(DateTime.FixedLocalNow()))) + Date.Month(Date.From(DateTime.FixedLocalNow()))), type number),
InsertYearOffset = Table.AddColumn(InsertMonthOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(DateTime.FixedLocalNow())), type number),
Note that this isn’t required but does simplify the DAX needed to calculate the measures.
Carry Over from Previous Year =
VAR Monthly_Revenue = CALCULATE( SUMX('Invoice Table', (1 - 'Invoice Table'[MonthNo]) * (-1) * [Total Amount(Monthly)]), FILTER( ALL( 'Date' ), 'Date'[YearOffset] = -1 ))
VAR Yearly_Revenue = CALCULATE( SUMX('Invoice Table', [Total Amount(Yearly)]/12 * (1 - 'Invoice Table'[MonthNo]) * (-1)), FILTER( ALL( 'Date' ), 'Date'[YearOffset] = -1 ))
RETURN
Monthly_Revenue + Yearly_Revenue
and for the second carryover measure.
Carry Over for the Previous 12 (actually, 11) Months =
VAR CurrentMonthOffset = CALCULATE( VALUES( 'Date'[MonthOffset] ), FIRSTDATE( 'Date'[Date] ))
VAR Monthly_Revenue = CALCULATE( SUMX('Invoice Table', (1 - 'Invoice Table'[MonthNo]) * (-1) * [Total Amount(Monthly)]), FILTER( ALL( 'Date' ), 'Date'[MonthOffset] >= CurrentMonthOffset-11 && 'Date'[MonthOffset] <= CurrentMonthOffset ))
VAR Yearly_Revenue = CALCULATE( SUMX('Invoice Table', [Total Amount(Yearly)]/12 * (1 - 'Invoice Table'[MonthNo]) * (-1)), FILTER( ALL( 'Date' ), 'Date'[MonthOffset] >= CurrentMonthOffset-11 && 'Date'[MonthOffset] <= CurrentMonthOffset ))
RETURN
Monthly_Revenue + Yearly_Revenue
With this result:
Here’s your file. I hope this is helpful.
Enterprise DNA - Measure Variations.pbix (123.3 KB)
2 Likes
Amazing stuff. Looks very good,
I see only one issue to address with the Carry Over from the Previous Year measure and you can blame my original explanation for not being 100% clear. No matter which Year is selected, the measure is currently not impacted given the “Filter (All” logic. However, the selected Year should result in the invoices dated Feb 1 - Dec 31 of the previous year being the basis for the measure. Thus, when 2019 is selected, 2/1/2018 - 12/31/2018 invoices of Type=‘Monthly’ or Type=‘Yearly’ are to be calculated. Knowing how to filter for that condition - filter by Year, but not by Month - is the key.
I’m attaching a PBIX with expanded invoice dates back into 2018 so that the impact of the selected Year can be more readily seen/tested.
Fantastic approach to the solution!!
Thank you,
Kevin
Enterprise DNA - Measure Variations 2.pbix (131.3 KB)
Glad to hear this is moving in the right direction 
That was actualy due to the filter condition that followed:
‘Date’[YearOffset] = -1
From our perspective now this will alway result in 2019 BUT in 2021 it would result in the year 2020.
So I made a small change - it now looks up the YearOffset for the selected year and then subtracts 1.
Carry Over from Entire Previous Year =
VAR CurrentYearOffset = CALCULATE( VALUES( 'Date'[YearOffset] ), FIRSTDATE( 'Date'[Date] ))
VAR Monthly_Revenue = CALCULATE( SUMX('Invoice Table', (1 - 'Invoice Table'[MonthNo]) * (-1) * [Total Amount(Monthly)]), FILTER( ALL( 'Date' ), 'Date'[YearOffset] = CurrentYearOffset-1 ))
VAR Yearly_Revenue = CALCULATE( SUMX('Invoice Table', [Total Amount(Yearly)]/12 * (1 - 'Invoice Table'[MonthNo]) * (-1)), FILTER( ALL( 'Date' ), 'Date'[YearOffset] = CurrentYearOffset-1 ))
RETURN
Monthly_Revenue + Yearly_Revenue
.
Here’s your file. Give it a go.
Enterprise DNA - Measure Variations 2.pbix (134.1 KB)
2 Likes
You nailed Carry Over from Entire Previous Year.
Further testing of ‘Carry Over for the Previous 12 (actually, 11) Months’ requires a better explanation and a correction.
Its basic definition: how much revenue in the selected month is generated from invoices dated in the previous 11 months
Its calculation:
Step 1: Sum Total Amount on invoices from the prior 11 months where Invoice Type = ‘Monthly’
Step 2: Sum (Total Amount / 12) from the prior 11 months where Invoice Type = ‘Yearly’
Step 3: Sum Step 1 and Step 2
As examples per the attached PBIX, invoices from 2018-Dec result in $82. in the above calcs and from 2019-Jan result in $350. Thus, with 2020-Feb selected as the filter/slicer value, the expected result is $432. for revenue generated in 2020-Feb from invoices in the prior 11 months.
In concept, if I wanted to know the ‘Carry Over for the Previous 12 (actually, 11) Months’ for Q1 2019 (or select those three months on the Slicer), I’d have 2018-Feb invoices * 1 … 2018-Mar invoices * 2 … 2018-Apr - 2018-Dec invoices * 3… 2019-Jan invoices * 2… 2019-Feb invoices * 1 as the calcs to produce the measure. Pretty complex stuff.
I’ll try to catch up re: how the offset works, and how the measures you added function, but hopefully the ‘Carry Over for the Previous 12 (actually, 11) Months’ can be addressed while I come up to speed.
Kevin
Enterprise DNA - Measure Variations 2.pbix (133.8 KB)
Hello,
In the attached PBIX, I believe I have the ‘Carry Over for the Previous 12 (actually, 11) Months’ measure working as desired with a month selected. Admittedly, my 2020-Feb figures were slightly off in manual calculations per my previous reply, but are now accurate.
As a lower priority, the selection of a Year without a MonthName selected (i.e. all months in the calendar Year) do not produce the expected ‘Carry Over for the Previous 12 (actually, 11) Months’ measure results. If you do have some thoughts on that, I’d welcome them. If not, just let me know and I’ll consider the topic as solved.
Thanks,
Kevin
Enterprise DNA - Measure Variations 2.pbix (143.0 KB)
What would you consider the desired outcome? If you can provide some more specific details I’m confident we can make it work.
Cool.
The existing ‘Carry Over for the Previous 12 (actually, 11) Months’ measure, when selecting multiple months, results in the 11 months prior to the first month being evaluated for invoices which produce revenue (e.g. Invoice Type = Monthly or Yearly) in the selected months. Specifically, selecting 2019-January produces the same results for this measure as selecting 2019-January, 2019-February, and 2019-March.
The desired results are to produce the measure for each selected month “individually” and then sum them. By '‘Carry Over for the Previous 12 (actually, 11) Months’ example,
2019-January = $79.00
2019-February = $426.50
2019-March = $1,366.70
Measure when all three months are selected = $1872.20
FYI, I could envision other variations of this measure, but if we can handle the above logic, I’m confidant I can adapt to any other variations.
Thanks,
Kevin
Enterprise DNA - Measure Variations 2.pbix (142.9 KB)
Amazing support on this Melissa well done.
1 Like
Thanks for that clear description Kevin.
Created a new measure that will include the total when there are multiple selections, like so:
Carry Over for the Previous 12 (actually, 11) Months incl. Total =
SUMX(
VALUES( 'Date'[MonthInCalendar] )|
[Carry Over for the Previous 12 (actually, 11) Months]
)
With this result:
.
Here’s your file. I hope this is helpful.
Enterprise DNA - Measure Variations 2.pbix (147.6 KB)
