I'm having an issue with a measure that works fine in Desktop but fails after publishing, I think because of the context filter issues with SUMMARIZECOLUMNS.
I'm using a dataset called "Combined Data" that looks like the following:
| FiscalYear | Fiscal Quarter | Client | Deal | Quote Year | Quote Effective Date | Quote Termination Date | Quarter Start Date | Quarter End Date | Actual Revenue | Quote Estimated Revenue | Row Index | Quote Last Row | Negligible |
| 2021 | 2021-Q1 | A | A | 2010 | | | 2021/01/01 | 2021/03/31 | | | 75 | False | False |
| 2021 | 2021-Q1 | A | B | 2011 | | | 2021/01/01 | 2021/03/31 | | | 76 | False | False |
| 2021 | 2021-Q1 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2021/01/01 | 2021/03/31 | | 150297.5 | 44 | False | False |
| 2021 | 2021-Q1 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2021/01/01 | 2021/03/31 | | 267745.98 | 52 | False | False |
| 2021 | 2021-Q1 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2021/01/01 | 2021/03/31 | | 267745.98 | 60 | False | False |
| 2021 | 2021-Q2 | A | B | 2011 | | | 2021/04/01 | 2021/06/30 | 7.93 | | 73 | False | False |
| 2021 | 2021-Q2 | A | A | 2010 | | | 2021/04/01 | 2021/06/30 | 17.48 | | 74 | False | False |
| 2021 | 2021-Q2 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2021/04/01 | 2021/06/30 | 17.53 | 150297.5 | 43 | False | False |
| 2021 | 2021-Q2 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2021/04/01 | 2021/06/30 | 688.39 | 267745.98 | 51 | False | False |
| 2021 | 2021-Q2 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2021/04/01 | 2021/06/30 | 26.51 | 267745.98 | 59 | False | False |
| 2021 | 2021-Q3 | A | A | 2010 | | | 2021/07/01 | 2021/09/30 | 17.14 | | 71 | False | False |
| 2021 | 2021-Q3 | A | B | 2011 | | | 2021/07/01 | 2021/09/30 | 7.77 | | 72 | False | False |
| 2021 | 2021-Q3 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2021/07/01 | 2021/09/30 | 17.19 | 150297.5 | 42 | False | False |
| 2021 | 2021-Q3 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2021/07/01 | 2021/09/30 | 675.21 | 267745.98 | 50 | False | False |
| 2021 | 2021-Q3 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2021/07/01 | 2021/09/30 | 26.03 | 267745.98 | 58 | False | False |
| 2021 | 2021-Q4 | A | B | 2011 | | | 2021/10/01 | 2021/12/31 | 7.54 | | 69 | False | False |
| 2021 | 2021-Q4 | A | A | 2010 | | | 2021/10/01 | 2021/12/31 | 16.6 | | 70 | False | False |
| 2021 | 2021-Q4 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2021/10/01 | 2021/12/31 | 16.63 | 150297.5 | 41 | True | False |
| 2021 | 2021-Q4 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2021/10/01 | 2021/12/31 | 654 | 267745.98 | 49 | True | False |
| 2021 | 2021-Q4 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2021/10/01 | 2021/12/31 | 25.2 | 267745.98 | 57 | True | False |
| 2022 | 2022-Q1 | A | B | 2011 | | | 2022/01/01 | 2022/03/31 | 6.6 | | 67 | False | False |
| 2022 | 2022-Q1 | A | A | 2010 | | | 2022/01/01 | 2022/03/31 | 14.53 | | 68 | False | False |
| 2022 | 2022-Q1 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2022/01/01 | 2022/03/31 | 14.56 | 150297.5 | 40 | False | False |
| 2022 | 2022-Q1 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2022/01/01 | 2022/03/31 | 571.99 | 267745.98 | 48 | False | False |
| 2022 | 2022-Q1 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2022/01/01 | 2022/03/31 | 22.05 | 267745.98 | 56 | False | False |
| 2022 | 2022-Q2 | A | B | 2011 | | | 2022/04/01 | 2022/06/30 | 5.77 | | 65 | False | False |
| 2022 | 2022-Q2 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2022/04/01 | 2022/06/30 | 12.78 | 150297.5 | 39 | False | False |
| 2022 | 2022-Q2 | A | A | 2010 | | | 2022/04/01 | 2022/06/30 | -1.4988E-15 | | 66 | False | True |
| 2022 | 2022-Q2 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2022/04/01 | 2022/06/30 | -1.71 | 267745.98 | 55 | False | False |
| 2022 | 2022-Q2 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2022/04/01 | 2022/06/30 | -44.35 | 267745.98 | 47 | False | False |
| 2022 | 2022-Q3 | A | B | 2011 | | | 2022/07/01 | 2022/09/30 | 5.46 | | 63 | False | False |
| 2022 | 2022-Q3 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2022/07/01 | 2022/09/30 | 12.08 | 150297.5 | 38 | False | False |
| 2022 | 2022-Q3 | A | A | 2010 | | | 2022/07/01 | 2022/09/30 | -4.85723E-17 | | 64 | False | True |
| 2022 | 2022-Q3 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2022/07/01 | 2022/09/30 | -1.78 | 267745.98 | 54 | False | False |
| 2022 | 2022-Q3 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2022/07/01 | 2022/09/30 | -46.16 | 267745.98 | 46 | False | False |
| 2022 | 2022-Q4 | A | B | 2011 | | | 2022/10/01 | 2022/12/31 | 5.54 | | 61 | False | False |
| 2022 | 2022-Q4 | A | C | 2015 | 2015/12/31 | 2016/12/30 | 2022/10/01 | 2022/12/31 | 12.26 | 150297.5 | 37 | True | False |
| 2022 | 2022-Q4 | A | A | 2010 | | | 2022/10/01 | 2022/12/31 | 1.51268E-15 | | 62 | False | True |
| 2022 | 2022-Q4 | A | E | 2012 | 2012/12/31 | 2013/12/30 | 2022/10/01 | 2022/12/31 | -1.87 | 267745.98 | 53 | True | False |
| 2022 | 2022-Q4 | A | D | 2013 | 2013/12/31 | 2014/12/30 | 2022/10/01 | 2022/12/31 | -48.75 | 267745.98 | 45 | True | False |
| 2021 | 2021-Q1 | B | F | 2011 | | | 2021/01/01 | 2021/03/31 | | | 36 | False | False |
| 2021 | 2021-Q2 | B | F | 2011 | | | 2021/04/01 | 2021/06/30 | 0.33 | | 35 | False | False |
| 2021 | 2021-Q3 | B | F | 2011 | | | 2021/07/01 | 2021/09/30 | 4.68 | | 34 | False | False |
| 2021 | 2021-Q4 | B | F | 2011 | | | 2021/10/01 | 2021/12/31 | 4.54 | | 33 | False | False |
| 2022 | 2022-Q1 | B | F | 2011 | | | 2022/01/01 | 2022/03/31 | 3.98 | | 32 | False | False |
| 2022 | 2022-Q2 | B | F | 2011 | | | 2022/04/01 | 2022/06/30 | 3.77 | | 31 | False | False |
| 2022 | 2022-Q3 | B | F | 2011 | | | 2022/07/01 | 2022/09/30 | 3.29 | | 30 | False | False |
| 2022 | 2022-Q4 | B | F | 2011 | | | 2022/10/01 | 2022/12/31 | 3.34 | | 29 | False | False |
| 2022 | 2022-Q1 | C | G | 2022 | 2022/01/01 | 2022/12/31 | 2022/01/01 | 2022/03/31 | 1195263.63 | 7961078.94 | 7 | False | False |
| 2022 | 2022-Q1 | C | H | 2021 | 2021/01/01 | 2021/12/31 | 2022/01/01 | 2022/03/31 | 1739761.6 | 9522905.24 | 14 | False | False |
| 2022 | 2022-Q1 | C | I | 2020 | 2020/01/01 | 2020/12/31 | 2022/01/01 | 2022/03/31 | | 9181739.69 | 21 | False | False |
| 2022 | 2022-Q1 | C | J | 2014 | 2014/06/15 | 2014/12/30 | 2022/01/01 | 2022/03/31 | | 2270329.6 | 28 | False | False |
| 2022 | 2022-Q2 | C | G | 2022 | 2022/01/01 | 2022/12/31 | 2022/04/01 | 2022/06/30 | -1102847.98 | 7961078.94 | 5 | False | False |
| 2022 | 2022-Q2 | C | H | 2021 | 2021/01/01 | 2021/12/31 | 2022/04/01 | 2022/06/30 | -1632043.92 | 9522905.24 | 13 | False | False |
| 2022 | 2022-Q2 | C | I | 2020 | 2020/01/01 | 2020/12/31 | 2022/04/01 | 2022/06/30 | 247.34 | 9181739.69 | 19 | False | False |
| 2022 | 2022-Q2 | C | J | 2014 | 2014/06/15 | 2014/12/30 | 2022/04/01 | 2022/06/30 | 117.87 | 2270329.6 | 27 | False | False |
| 2022 | 2022-Q3 | C | G | 2022 | 2022/01/01 | 2022/12/31 | 2022/07/01 | 2022/09/30 | 8.52651E-14 | 7961078.94 | 3 | False | True |
| 2022 | 2022-Q3 | C | H | 2021 | 2021/01/01 | 2021/12/31 | 2022/07/01 | 2022/09/30 | 7.28306E-14 | 9522905.24 | 10 | False | True |
| 2022 | 2022-Q3 | C | J | 2014 | 2014/06/15 | 2014/12/30 | 2022/07/01 | 2022/09/30 | -1.33227E-14 | 2270329.6 | 25 | False | True |
| 2022 | 2022-Q3 | C | I | 2020 | 2020/01/01 | 2020/12/31 | 2022/07/01 | 2022/09/30 | 8.21565E-15 | 9181739.69 | 18 | False | True |
| 2022 | 2022-Q4 | C | G | 2022 | 2022/01/01 | 2022/12/31 | 2022/10/01 | 2022/12/31 | 2.38032E-13 | 7961078.94 | 1 | True | True |
| 2022 | 2022-Q4 | C | H | 2021 | 2021/01/01 | 2021/12/31 | 2022/10/01 | 2022/12/31 | -1.47438E-13 | 9522905.24 | 9 | False | True |
| 2022 | 2022-Q4 | C | J | 2014 | 2014/06/15 | 2014/12/30 | 2022/10/01 | 2022/12/31 | 7.88258E-15 | 2270329.6 | 23 | False | True |
| 2022 | 2022-Q4 | C | I | 2020 | 2020/01/01 | 2020/12/31 | 2022/10/01 | 2022/12/31 | -1.04361E-14 | 9181739.69 | 15 | True | True |
It is the result of merging two sets of data with different sorts of time granularities - a financial dataset with actual revenue by fiscal quarter and quote year, and a quotes dataset which has the effective date and termination date of different deals, as well as the revenue that was projected as at quote time. Basically I'm getting the most recent quote applicable at each fiscal period, and using the quote year to keep older quotes that are still applicable. The row index is calculated in the query based on a sort by Company, Deal, Quote Effective Date and Fiscal Quarter, in order to identify the most recent row that I want to use, and quote last row is a calculated column such that it identifies whether the row is the last for a given quote and a given fiscal year.
I also have a calendar table "Calendar" covering the period, with a join from "Date" to "Quarter Start Date", and a disconnected table with a single column called "Include" which is either True/False that I'm using as a switch (see below).
I'm trying to produce visuals that show the most recent quote revenue to contrast actuals. If I have date as an axis it needs to show the most recent revenue as at that date (in practice I'm not drilling down further than year), but it needs to roll up correctly into subtotals and grand totals (by date) for any other dimensions (company, deal, and many other columns in the real dataset). The measure I'm using in Desktop is as follows, and it works as I want it to:
Latest Quote Revenue USD =
VAR includeclosed = SELECTEDVALUE('Closed Business'[Include], TRUE) // This is the disconnected table/slicer switch - not too important
VAR filtered = FILTER('Combined Data', 'Combined Data'[Quarter Start Date] < 'Combined Data'[Quote Termination Date] || ('Combined Data'[Negligible] = FALSE && includeclosed = TRUE))
VAR summ = SUMMARIZECOLUMNS('Combined Data'[Company], 'Calendar'[Year], filtered, "val",
CALCULATE(MAX('Combined Data'[Quote Estimated Revenue]), FILTER(filtered, 'Combined Data'[Row Index]=MAX('Combined Data'[Row Index]))))
RETURN SUMX(summ, [val])
Unfortunately this doesn't work in Fabric for me, and I understand this is because of limitations with SUMMARIZECOLUMNS there. (Calculation error in measure 'Combined Data'[Latest Quote Revenue USD]: SummarizeColumns() and AddMissingItems() may not be used in this context.)
What is the correct way to translate this to not use SUMMARIZECOLUMNS? Is there a better approach I could be using overall?
