It's linking last names when it should only be linking dates

[#1]

Hi!

I have a master calendar that my data is supposed to link to. That's all it's supposed to link to in this code:

=money(if(GetFieldSelections(FiscalMonthYear), sum({<[Order reason]={'2'}>}[Total Revenue]),

     if(GetFieldSelections(FiscalQuarterYear), sum({<[Order reason]={'2'}>}[Total Revenue]),

        if(GetFieldSelections(FiscalMonth), sum({<[Order reason]={'2'}>}[Total Revenue]),

          if(GetFieldSelections(FiscalQuarter), sum({<[Order reason]={'2'}>}[Total Revenue]),

           if(IsNull([FiscalYear]), '-',

            if(GetFieldSelections([FiscalYear]),sum({<[Order reason]={'2'}>}[Total Revenue]))))))))

This is all in a text box, by the way.

However, it is also taking into account Last Name. There is no last name in the spreadsheet, and the sheet isn't concatenating to anything. I even said NoConcatenate. What is going on and how can I make it display '-' in the text box?

Thanks so much!

[sunny_talwar]

Your MasterCalendar is linking to your fact table on Date and Last Name? Master Calendar script should not have Last Name column. You should probably remove it and may be the thing start to work.

Best,

Sunny

[#3]

That's the thing: the master calendar doesn't have a last name column. I have no idea why it would be affected by last name when there's no last name in the master calendar and it's not in the spreadsheet.

[swuehl]

What do you mean by 'taking into account Last Name'?

And how does your data model look like? Please post (if possible) your QVW file. Or some more informations, the document log, a screenshot of your table view, anything would be helpful.

With the information provided it's just guessing.

[sunny_talwar]

Can you share your script for the master calendar and if possible your complete script?

[#6]

So I'm trying to sum something by order reason. This can happen in a period. However, it cannot be affected by person. So last name shouldn't show up, I attached my data model. GMR and MASTER_C[ALENDAR] are the tables in question.

[sunny_talwar]

Your fact table is only connected on DATE to master calendar. the issue seems to be something related to your front end expression. What exactly is the issue?

[#8]

First is the Master Calendar Script. At the bottom is the GMR script.

Master Calendar:

//***********************VARIABLES***********************//

// The last year of your Master Calendar

Let vLastYear= Year($(vToday)) + 1;

// The end MM/DD date for your accounting year

Let vLastYearDate = '01/03/' & $(vLastYear);

//Paste the folder path to the Accounting Periods QVD here

LET vAccountingPeriodPath = '\\TXAAPPNWH832V\EZGO_MasterData\QVD\Accounting Calendar\';

//***********************ACCOUNTING PERIODS***********************//

//Load accounting periods: START_DATE and END_DATE are equivalent to Month Start and End,

ACCOUNTING_PERIOD:

LOAD START_DATE, //Corrensponds to Month Start

  Num(START_DATE) AS START,

  END_DATE, //Corrensponds to Month End

  Num(END_DATE) AS END,

    ENTERED_PERIOD_NAME,

    PERIOD_NAME,

    PERIOD_NUM,

    PERIOD_YEAR,

    QUARTER_NUM,

    MakeDate(PERIOD_YEAR, PERIOD_NUM) AS PERIOD_DATE,

    AutoNumberHash128(QUARTER_NUM, PERIOD_YEAR) AS ACCOUNTING_PERIOD_QUARTER_ID //Need to create a unique ID to join in Quarter stand and end once created

FROM

[$(vAccountingPeriodPath)AccountingPeriods.qvd]

(qvd);

// Create the start and end dates for the Quarters

LEFT JOIN(ACCOUNTING_PERIOD)

LOAD AutoNumberHash128(QUARTER_NUM, PERIOD_YEAR) AS ACCOUNTING_PERIOD_QUARTER_ID,

  Date(MAX(END_DATE), 'MM/DD/YYYY') AS QUARTER_END_DATE,

  Date(MIN(START_DATE), 'MM/DD/YYYY') AS QUARTER_START_DATE

FROM

[$(vAccountingPeriodPath)AccountingPeriods.qvd]

(qvd)

WHERE PERIOD_YEAR >= year($(vMinDate)) AND PERIOD_YEAR < $(vLastYear)

GROUP BY PERIOD_YEAR, QUARTER_NUM;

//Create the start and end dates for the year

LEFT JOIN(ACCOUNTING_PERIOD)

LOAD PERIOD_YEAR,

  Date(MAX(END_DATE), 'MM/DD/YYYY') AS YEAR_END_DATE,

  Date(MIN(START_DATE), 'MM/DD/YYYY') AS YEAR_START_DATE

FROM

[$(vAccountingPeriodPath)AccountingPeriods.qvd]

(qvd)

WHERE PERIOD_YEAR >= year($(vMinDate)) AND PERIOD_YEAR < $(vLastYear)

GROUP BY PERIOD_YEAR;

//TODO: Why isn't the min date coming from here.

TEMP:

LOAD

  num(max(END_DATE)) AS MaxDate

RESIDENT ACCOUNTING_PERIOD

WHERE Num(Date(END_DATE)) < Num(Date('$(vLastYearDate)', 'MM/DD/YYYY'));

LET vMaxDate = peek('MaxDate', 0, 'TEMP');

DROP TABLE TEMP;

DateIsland:

LOAD

  date($(vMinDate) + rowno() - 1) AS D,

  year($(vMinDate) + rowno() - 1) AS Y,

  month($(vMinDate) + rowno() - 1) AS M,

  date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY

AUTOGENERATE vMaxDate - vMinDate + 1;

MASTER_CALENDAR:

LOAD

  D AS DATE,

  Y AS Year,

  M AS Month,

  MY AS MonthYear,

  Num(M) & Y as MonthNumYear,

  'Q' & ceil(M / 3) AS Quarter,

  'Q' & ceil(M / 3) & '-' & Y as QuarterYear,

  week(D) & '-' & year(D) AS Week,

  week(D) AS WeekNumber,

  weekday(D) AS Weekday,

  day(D) AS Day,

  date(D, 'MM/DD') AS DateMMDD

RESIDENT

  DateIsland;

DROP TABLE DateIsland;

//BEGIN Adding the Accounting Period intervals to master calendar

IntervalMatch(DATE) //Match DATE into the Accounting Period table

LEFT JOIN(ACCOUNTING_PERIOD)

LOAD

  START_DATE,

  END_DATE

RESIDENT ACCOUNTING_PERIOD;

//DROP FIELDS START_DATE, END_DATE; //no longer need the range values.

//JOIN in the accounting period dates into the Master Calendar

ACCOUNTING_PERIOD_JOIN:

LEFT JOIN (MASTER_CALENDAR)

LOAD

  Date(DATE, 'MM/DD/YYYY') AS DATE,

  Date(START_DATE, 'MM/DD/YYYY') AS MONTH_START_DATE,

  Num(START_DATE) AS MONTH_START,

  Date(END_DATE, 'MM/DD/YYYY') AS MONTH_END_DATE,

  Num(END_DATE) AS MONTH_END,

  Date(QUARTER_START_DATE, 'MM/DD/YYYY') AS QUARTER_START_DATE,

  Num(QUARTER_START_DATE) AS QUARTER_START,

  Date(QUARTER_END_DATE, 'MM/DD/YYYY') AS QUARTER_END_DATE,

  Num(QUARTER_END_DATE) AS QUARTER_END,

  Date(YEAR_START_DATE, 'MM/DD/YYYY') AS YEAR_START_DATE,

  Num(YEAR_START_DATE) AS YEAR_START,

  Date(YEAR_END_DATE, 'MM/DD/YYYY') AS YEAR_END_DATE,

  Num(YEAR_END_DATE) AS YEAR_END,

    MakeDate(PERIOD_YEAR, PERIOD_NUM) AS PERIOD_DATE,

  ENTERED_PERIOD_NAME,

  PERIOD_NUM,

  PERIOD_NAME,

  PERIOD_YEAR,

  QUARTER_NUM,

  AutoNumberHash128(QUARTER_NUM, PERIOD_YEAR) AS ACCOUNTING_PERIOD_QUARTER_ID

RESIDENT ACCOUNTING_PERIOD;

//In order to align the date flags with the accounting periods - get the current relevant dates.

CURRENT_TEMP:

LOAD

  MONTH_START_DATE,

  MONTH_END_DATE,

  QUARTER_START_DATE,

  QUARTER_END_DATE,

  YEAR_START_DATE,

  YEAR_END_DATE,

  PERIOD_NUM,

  PERIOD_YEAR,

  QUARTER_NUM

RESIDENT MASTER_CALENDAR

WHERE DATE = $(vToday);

LET vCurrentMonthStart = floor(peek('MONTH_START_DATE', 0, 'CURRENT_TEMP'));

LET vCurrentMonthEnd = floor(peek('MONTH_END_DATE', 0, 'CURRENT_TEMP'));

LET vCurrentQuarterStart = floor(peek('QUARTER_START_DATE', 0, 'CURRENT_TEMP'));

LET vCurrentQuarterEnd = floor(peek('QUARTER_END_DATE', 0, 'CURRENT_TEMP'));

LET vCurrentYearStart = floor(peek('YEAR_START_DATE', 0, 'CURRENT_TEMP'));

//LET vPriorYearStart = Num(Date('01/01/2012', 'MM/DD/YYYY')); ;

LET vCurrentYearEnd = floor(peek('YEAR_END_DATE', 0, 'CURRENT_TEMP'));

LET vDaysIntoMonth = $(vToday) - $(vCurrentMonthStart) + 1;

LET vDaysLeftInMonth = $(vCurrentMonthEnd) - $(vToday);

LET vDaysInMonth = $(vCurrentMonthEnd) - $(vCurrentMonthStart) + 1;

LET vDaysIntoQuarter = $(vToday) - $(vCurrentQuarterStart) + 1;

LET vDaysLeftInQuarter = $(vCurrentQuarterEnd) - $(vToday);

LET vDaysInQuarter = $(vCurrentQuarterEnd) - $(vCurrentQuarterStart) + 1;

LET vDaysIntoYear = $(vToday) - $(vCurrentYearStart) + 1;

LET vDaysLeftInYear = $(vCurrentYearEnd) - $(vToday);

LET vDaysInYear = $(vCurrentYearEnd) - $(vCurrentYearStart) + 1;

LET vCurrentMonth = floor(peek('PERIOD_NUM', 0, 'CURRENT_TEMP'));

LET vCurrentYear = floor(peek('PERIOD_YEAR', 0, 'CURRENT_TEMP'));

LET vCurrentQuarter = floor(peek('QUARTER_NUM', 0, 'CURRENT_TEMP'));

LET vWorkDaysIntoMonth = networkdays($(vCurrentMonthStart), $(vToday));

LET vWorkDaysLeftInMonth = networkdays($(vToday), $(vCurrentMonthEnd));

LET vWorkDaysInMonth = networkdays($(vCurrentMonthStart), $(vCurrentMonthEnd));

LET vWorkDaysIntoQuarter = networkdays($(vCurrentQuarterStart), $(vToday));

LET vWorkDaysLeftInQuarter = networkdays($(vToday), $(vCurrentQuarterEnd));

LET vWorkDaysInQuarter = networkdays($(vCurrentQuarterStart), $(vCurrentQuarterEnd));

LET vWorkDaysIntoYear = networkdays($(vCurrentYearStart), $(vToday));

LET vWorkDaysLeftInYear = networkdays($(vToday), $(vCurrentYearEnd));

LET vWorkDaysInYear = networkdays($(vCurrentYearStart), $(vCurrentYearEnd));

SET vDaysLeftInPeriod = if($(vMTD) = 1, $(vDaysLeftInMonth), if($(vQTD) = 1, $(vDaysLeftInQuarter), $(vDaysLeftInYear)));

SET vDaysIntoPeriod = if($(vMTD) = 1, $(vDaysIntoMonth), if($(vQTD) = 1, $(vDaysIntoQuarter), $(vDaysIntoYear)));

SET vDaysInPeriod = if($(vMTD) = 1, $(vDaysInMonth), if($(vQTD) = 1, $(vDaysInQuarter), $(vDaysInYear)));

SET vWorkDaysLeftInPeriod = if($(vMTD) = 1, $(vWorkDaysLeftInMonth), if($(vQTD) = 1, $(vWorkDaysLeftInQuarter), $(vWorkDaysLeftInYear)));

SET vWorkDaysIntoPeriod = if($(vMTD) = 1, $(vWorkDaysIntoMonth), if($(vQTD) = 1, $(vWorkDaysIntoQuarter), $(vWorkDaysIntoYear)));

SET vWorkDaysInPeriod = if($(vMTD) = 1, $(vWorkDaysInMonth), if($(vQTD) = 1, $(vWorkDaysInQuarter), $(vWorkDaysInYear)));

PRIOR_TEMP:

NOCONCATENATE LOAD

  MONTH_START_DATE,

  MONTH_END_DATE,

  QUARTER_START_DATE,

  QUARTER_END_DATE,

  YEAR_START_DATE,

  YEAR_END_DATE,

  PERIOD_NUM,

  PERIOD_YEAR,

  QUARTER_NUM

RESIDENT MASTER_CALENDAR

WHERE PERIOD_NUM = $(vCurrentMonth)

AND PERIOD_YEAR = $(vCurrentYear)-1

AND QUARTER_NUM = $(vCurrentQuarter);

LET vPriorMonthStart = floor(peek('MONTH_START_DATE', 0, 'PRIOR_TEMP'));

LET vPriorMonthEnd = floor(peek('MONTH_END_DATE', 0, 'PRIOR_TEMP'));

LET vPriorQuarterStart = floor(peek('QUARTER_START_DATE', 0, 'PRIOR_TEMP'));

LET vPriorQuarterEnd = floor(peek('QUARTER_END_DATE', 0, 'PRIOR_TEMP'));

LET vPriorYearStart = floor(peek('YEAR_START_DATE', 0, 'PRIOR_TEMP'));

LET vPriorYearStart = Num(Date('12/30/2012', 'MM/DD/YYYY')); ;

LET vPriorYearEnd = floor(peek('YEAR_END_DATE', 0, 'PRIOR_TEMP'));

LET vDaysIntoMonth = $(vToday) - $(vPriorMonthStart) + 1;

LET vDaysLeftInMonth = $(vPriorMonthEnd) - $(vToday);

LET vDaysInMonth = $(vPriorMonthEnd) - $(vPriorMonthStart) + 1;

LET vDaysIntoQuarter = $(vToday) - $(vPriorQuarterStart) + 1;

LET vDaysLeftInQuarter = $(vPriorQuarterEnd) - $(vToday);

LET vDaysInQuarter = $(vPriorQuarterEnd) - $(vPriorQuarterStart) + 1;

LET vDaysIntoYear = $(vToday) - $(vPriorYearStart) + 1;

LET vDaysLeftInYear = $(vPriorYearEnd) - $(vToday);

LET vDaysInYear = $(vPriorYearEnd) - $(vPriorYearStart) + 1;

DROP TABLE PRIOR_TEMP;

FUTURE_QUARTER:

NOCONCATENATE LOAD

  MONTH_START_DATE,

  MONTH_END_DATE,

  QUARTER_START_DATE,

  QUARTER_END_DATE,

  YEAR_START_DATE,

  YEAR_END_DATE,

  PERIOD_NUM,

  PERIOD_YEAR,

  QUARTER_NUM

RESIDENT MASTER_CALENDAR

WHERE PERIOD_NUM = Num(Month(AddMonths($(vToday),3)))

AND PERIOD_YEAR = Year(AddMonths($(vToday),3));

LET vFutureQuarterStart = floor(peek('QUARTER_START_DATE', 0, 'FUTURE_QUARTER'));

LET vFutureQuarterEnd = floor(peek('QUARTER_END_DATE', 0, 'FUTURE_QUARTER'));

DROP TABLE FUTURE_QUARTER;

PREVIOUS_QUARTER:

NOCONCATENATE LOAD

  MONTH_START_DATE,

  MONTH_END_DATE,

  QUARTER_START_DATE,

  QUARTER_END_DATE,

  YEAR_START_DATE,

  YEAR_END_DATE,

  PERIOD_NUM,

  PERIOD_YEAR,

  QUARTER_NUM

RESIDENT MASTER_CALENDAR

WHERE PERIOD_NUM = Num(Month(AddMonths($(vToday),-3)))

AND PERIOD_YEAR = Year(AddMonths($(vToday),-3));

LET vPreviousQuarterStart = floor(peek('QUARTER_START_DATE', 0, 'PREVIOUS_QUARTER'));

LET vPreviousQuarterEnd = floor(peek('QUARTER_END_DATE', 0, 'PREVIOUS_QUARTER'));

DROP TABLE PREVIOUS_QUARTER;

//TODO: This is probably unnecessary now.

CurrentPeriodDetails:

LOAD * INLINE [

        CurrentMonthStart, CurrentMonthEnd, CurrentQuarterStart, CurrentQuarterEnd, CurrentYearStart, CurrentYearEnd, DaysIntoMonth, DaysIntoQuarter, DaysIntoYear, DaysLeftInMonth, DaysLeftInQuarter, DaysLeftInYear, DaysInMonth, DaysInQuarter, DaysInYear

        $(vCurrentMonthStart), $(vCurrentMonthEnd), $(vCurrentQuarterStart), $(vCurrentQuarterEnd), $(vCurrentYearStart), $(vCurrentYearEnd), $(vDaysIntoMonth), $(vDaysIntoQuarter), $(vDaysIntoYear), $(vDaysLeftInMonth), $(vDaysLeftInQuarter), $(vDaysLeftInYear), $(vDaysInMonth), $(vDaysInQuarter), $(vDaysInYear)

];

DROP TABLE CURRENT_TEMP;

//Can't use the built in date functions as the accounting periods are offset by days in the 4-4-5 model. E.g. April 2013 may begin on 03/31/2013 and End on 04/29/2013

TO_DATE_FLAGS_JOIN:

LEFT JOIN (MASTER_CALENDAR)

LOAD

// Rolling 12

  if(DN > addmonths($(vToday),-3) and DN <= Today(),1, 0) as Rolling_3,

  if(DN > addmonths($(vToday),-12) and DN <= Today(),1, 0) as Rolling_12,

  if(DN > addmonths($(vToday),-24) and DN <= Today(),1, 0) as Rolling_24,

  if(DN < addmonths($(vToday),3) and DN >= Today(),1, 0) as Forward_3,

  if(DN < addmonths($(vToday),12) and DN >= Today(),1, 0) as Forward_12,

  if(DN < addmonths($(vToday),24) and DN >= Today(),1, 0) as Forward_24,

// Year-to-date flags

  if(Num(DN) >= $(vCurrentYearStart) AND Num(DN) <= Num($(vToday)) AND CY = 1, 1, 0) AS CYTD,

  if(Num(DN) >= $(vPriorYearStart) AND DN <= AddMonths($(vToday), -12) AND FPY = 1, 1, 0) AS FPYTD,

  if(Num(DN) >= $(vPriorYearStart) AND DN <= Num($(vPriorMonthEnd)) AND FPY = 1, 1, 0) AS FPYFMTD,

// if(DN >= AddMonths($(vCurrentYearStart), -12) AND DN <= AddMonths($(vToday), -12) AND FPY = 1, 1, 0) AS FPYTD,

  if(DN >= AddMonths($(vCurrentYearStart), -24) AND DN <= AddMonths($(vToday), -24) AND SPY = 1, 1, 0) AS SPYTD,

// Quarter-to-date flags

  if(DN >= $(vCurrentQuarterStart) AND DN <= $(vToday) AND CQ = 1, 1, 0) AS CQTD,

  if(DN >= AddMonths($(vCurrentQuarterStart), -12) AND DN <= AddMonths($(vToday), -12) AND FPQ = 1, 1, 0) AS FPQTD,

  if(DN >= AddMonths($(vCurrentQuarterStart), -24) AND DN <= AddMonths($(vToday), -24) AND SPQ = 1, 1, 0) AS SPQTD,

// Month-to-date flags

  if(DN >= $(vCurrentMonthStart) AND DN <= $(vToday) AND CM = 1, 1, 0) AS CMTD,

  if(DN >= AddMonths($(vCurrentMonthStart), -1) AND DN <= AddMonths($(vToday), -1) AND PM = 1, 1, 0) AS PMTD,

  if(DN >= AddMonths($(vCurrentMonthStart), -12) AND DN <= AddMonths($(vToday), -12) AND FPM = 1, 1, 0) AS FPMTD,

  if(Num(DN) >= $(vPriorMonthStart) AND DN <= Num($(vPriorMonthEnd)) AND FPY = 1, 1, 0) AS FPFM, //First Prior Year Full Month

  if(DN >= AddMonths($(vCurrentMonthStart), -24) AND DN <= AddMonths($(vToday), -24) AND SPM = 1, 1, 0) AS SPMTD,

//***********************MASTER FISCAL CALENDAR***********************//

// Previous Day Flag

// if(DN = $(vToday) - 1, 1, 0) AS PD, OLD

        if(WeekDay(DN) = '4' AND DN = $(vToday)-3,1,if(WeekDay(DN) = '4' and DN = $(vToday) -2,1,if((WeekDay(DN) >= 0 AND WeekDay(DN) <= 4) AND DN = $(vToday) - 1,1,0))) as PD,

//  Week Day Flag

  If(WeekDay(DN) >= 0 AND WeekDay(DN) < 5, 1, 0) AS WeekDayFlag,

  *;

LOAD

// Year-to-date flags

  if(DN >= $(vCurrentYearStart) AND DN <= $(vCurrentYearEnd), 1, 0) AS CY,

  if(DN >= AddMonths($(vCurrentYearStart), -12) AND DN <= AddMonths($(vCurrentYearEnd), -12), 1, 0) AS FPY,

  if(DN >= AddMonths($(vCurrentYearStart), -24) AND DN <= AddMonths($(vCurrentYearEnd), -24), 1, 0) AS SPY,

// Quarter-to-date flags

  if(DN >= $(vCurrentQuarterStart) AND DN <= $(vCurrentQuarterEnd), 1, 0) AS CQ,

  if(DN >= $(vFutureQuarterStart) AND DN <= $(vFutureQuarterEnd), 1, 0) AS FQ,

  if(DN >= $(vPreviousQuarterStart) AND DN <= $(vPreviousQuarterEnd), 1, 0) AS PQ,

  if(DN >= AddMonths($(vCurrentQuarterStart), -12) AND DN <= AddMonths($(vCurrentQuarterEnd), -12), 1, 0) AS FPQ,

  if(DN >= AddMonths($(vCurrentQuarterStart), -24) AND DN <= AddMonths($(vCurrentQuarterEnd), -24), 1, 0) AS SPQ,

// Month-to-date flag

  if(DN >= $(vCurrentMonthStart) AND DATE <= $(vCurrentMonthEnd), 1, 0) AS CM,

  if(DN >= AddMonths($(vCurrentMonthStart), -1) AND DN <= AddMonths($(vCurrentMonthEnd), -1), 1, 0) AS PM,

  if(DN >= AddMonths($(vCurrentMonthStart), -12) AND DN <= AddMonths($(vCurrentMonthEnd), -12), 1, 0) AS FPM,

  if(DN >= AddMonths($(vCurrentMonthStart), -24) AND DN <= AddMonths($(vCurrentMonthEnd), -24), 1, 0) AS SPM,

  *;

LOAD DATE,

  Num(DATE) AS DN

RESIDENT MASTER_CALENDAR;

LEFT JOIN (MASTER_CALENDAR)

LOAD DATE,

    ENTERED_PERIOD_NAME AS FiscalMonth,

    PERIOD_NAME AS FiscalMonthYear,

    PERIOD_YEAR AS FiscalYear,

    'Q' & QUARTER_NUM AS FiscalQuarter,

    'Q' & QUARTER_NUM & '-' & PERIOD_YEAR AS FiscalQuarterYear

RESIDENT ACCOUNTING_PERIOD;

CURRENT_TEMP:

LOAD

  FiscalYear,

  FiscalQuarter,

  FiscalMonth

RESIDENT MASTER_CALENDAR

WHERE DATE = $(vToday);

LET vCurrentFiscalMonth = peek('FiscalMonth', 0, 'CURRENT_TEMP');

LET vCurrentFiscalQuarter = peek('FiscalQuarter', 0, 'CURRENT_TEMP');

LET vCurrentFiscalYear = floor(peek('FiscalYear', 0, 'CURRENT_TEMP'));

//LET vCurrencyRate = if($(vCurrentFiscalYear) ='2015',$(Include=\\txaappnwh832v\jacqvdev\_masterdata\data\flatfiles\currencyconversion.txt),0);

DROP TABLE CURRENT_TEMP;

DROP TABLE ACCOUNTING_PERIOD;

GMR Script:

NoConcatenate

GMR:

LOAD      [Posting Date] as DATE,

    [Total Revenue],

    [Total Cost],

    [Gross Margin($)],

    [Gross margin(%)],

    Freight,

    [Profit Center],

    Customer,

    Quantity,

    [Sales Order],

    [Distribution Channel],

    [Customer group],

    State,

    [Order reason],

    Customer1 as [Unique ID]

FROM

(ooxml, embedded labels, table is SQE);

[#9]

It's modifying the results based on last name when it should only change based on date selections. Here's the expression code again:

=money(if(GetFieldSelections(FiscalMonthYear), sum({<[Order reason]={'2'}>}[Total Revenue]),

     if(GetFieldSelections(FiscalQuarterYear), sum({<[Order reason]={'2'}>}[Total Revenue]),

        if(GetFieldSelections(FiscalMonth), sum({<[Order reason]={'2'}>}[Total Revenue]),

          if(GetFieldSelections(FiscalQuarter), sum({<[Order reason]={'2'}>}[Total Revenue]),

           if(IsNull([FiscalYear]), '-',

            if(GetFieldSelections([FiscalYear]),sum({<[Order reason]={'2'}>}[Total Revenue]))))))))

[sunny_talwar · Accepted solution]

Try this:

=Money(

if(GetFieldSelections(FiscalMonthYear), Sum({<[Order reason]={'2'}, [Last Name] = >}[Total Revenue]),

if(GetFieldSelections(FiscalQuarterYear), Sum({<[Order reason]={'2'}, [Last Name] = >}[Total Revenue]),

if(GetFieldSelections(FiscalMonth), Sum({<[Order reason]={'2'}, [Last Name] = >}[Total Revenue]),

if(GetFieldSelections(FiscalQuarter), Sum({<[Order reason]={'2'}, [Last Name] = >}[Total Revenue]),

if(IsNull(Only({<, [Last Name] = >}[FiscalYear])), '-',

if(GetFieldSelections([FiscalYear]), Sum({<[Order reason]={'2'}, [Last Name] = >}[Total Revenue]))))))))