Use one measure value as an attribute for another measure

[Tejas007]

I have one measure -

Backlog GM Percent =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())

-- Select distinct fiscal months for the current and previous year
VAR DistinctMonths =
DISTINCT(
SELECTCOLUMNS(
FILTER(
ALL('dm_dim_val_fiscal_calendar'),
('dm_dim_val_fiscal_calendar'[Fiscal_Year_No] = CurrentYear &&
'dm_dim_val_fiscal_calendar'[Fiscal_Month_No] <= currentMonth)||
(dm_dim_Val_fiscal_calendar[Fiscal_Year_No] = CurrentYear - 1 &&
dm_dim_Val_fiscal_calendar[Fiscal_Month_No] > CurrentMonth)),
"FiscalYear", dm_dim_Val_fiscal_calendar[Fiscal_Year_No],
"FiscalMonth", dm_dim_Val_fiscal_calendar[Fiscal_Month_No]
)
)
-- Get the last 3 distinct months
VAR lastThreeMonths =
TOPN(
3,
distinctMonths,
[FiscalYear] * 100 + [FiscalMonth],
DESC
)
-- Calculate the average of GM% for the last three months
VAR AverageGM =
AVERAGEX(
LastThreeMonths,
[GM % at STD W freight]
)
RETURN
AverageGM

This gives me average GM % Backlog value, which is based on average of last 3 months- [GM % at STD W freight]. I have GM% at std W freight only for current and past months of a year.

Now I want to use this measure to extrapolate GM $ for backlog for future months. Formula for that is-

Backlog GM $ =

IF(

    HASONEVALUE('dm_dim_val_fiscal_calendar'[fiscal_month_no]),

    IF(

        [Released Backlog Sales $] = 0,

        BLANK(),

        [Released Backlog Sales $] * ('Backlog Measures'[Backlog GM Percent] / 100)

    ),

    SUMX(

        VALUES('dm_dim_val_fiscal_calendar'[fiscal_month_no]),

        [Released Backlog Sales $] * ('Backlog Measures'[Backlog GM Percent] / 100)

    )

)
If I use my Backlog GM Percent in above measure, I don't see GM $ values for future months (any month in future other than current month for a calendar year). How can I ensure that I see the GM $ values for future months too?

[Anonymous]

Hi @Tejas007 ,

Could you please show more details about the question? If there is a pbix file, it would be better.

Thanks for @rajendraongole1 reply and contributions to the community.

Best regards,

Mengmeng Li

[rajendraongole1]

Hi @Tejas007 -modify this measure so that it returns the calculated value for past months and extends the same value for future months.

Backlog GM Percent =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())

-- Select distinct fiscal months for the current and previous year
VAR DistinctMonths =
DISTINCT(
SELECTCOLUMNS(
FILTER(
ALL('dm_dim_val_fiscal_calendar'),
('dm_dim_val_fiscal_calendar'[Fiscal_Year_No] = CurrentYear &&
'dm_dim_val_fiscal_calendar'[Fiscal_Month_No] <= CurrentMonth) ||
('dm_dim_val_fiscal_calendar'[Fiscal_Year_No] = CurrentYear - 1 &&
'dm_dim_val_fiscal_calendar'[Fiscal_Month_No] > CurrentMonth)
),
"FiscalYear", 'dm_dim_val_fiscal_calendar'[Fiscal_Year_No],
"FiscalMonth", 'dm_dim_val_fiscal_calendar'[Fiscal_Month_No]
)
)

-- Get the last 3 distinct months
VAR LastThreeMonths =
TOPN(
3,
DistinctMonths,
[FiscalYear] * 100 + [FiscalMonth],
DESC
)

-- Calculate the average of GM% for the last three months
VAR AverageGM =
AVERAGEX(
LastThreeMonths,
[GM % at STD W freight]
)

-- Determine if the current date is in a future month and return the calculated GM% for future months
RETURN
IF (
'dm_dim_val_fiscal_calendar'[Fiscal_Year_No] > CurrentYear ||
('dm_dim_val_fiscal_calendar'[Fiscal_Year_No] = CurrentYear &&
'dm_dim_val_fiscal_calendar'[Fiscal_Month_No] > CurrentMonth),
AverageGM, -- Use the same GM% for future months
AverageGM -- Use the calculated value for past months (or adjust here if needed)
)

Now, using the above measure, we will update the Backlog GM $ calculation. This will ensure the formula works for future months as well.

Backlog GM $ =
IF(
HASONEVALUE('dm_dim_val_fiscal_calendar'[fiscal_month_no]),
IF(
[Released Backlog Sales $] = 0,
BLANK(),
[Released Backlog Sales $] * ( 'Backlog Measures'[Backlog GM Percent] / 100 )
),
SUMX(
VALUES('dm_dim_val_fiscal_calendar'[fiscal_month_no]),
[Released Backlog Sales $] * ( 'Backlog Measures'[Backlog GM Percent] / 100 )
)
)

Hope the above two measures helps in your scenerio.

[Tejas007]

A single value for column 'Fiscal_Year_No' in table 'dm_dim_val_fiscal_calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Thanks for responding, I got the above error for fiscal_year_no and Fiscal_month_no. It's not a typo error. Column name is Fiscal_year_no and Fiscal_Month_No. I got that error in the IF statement. 


EDIT- I solved the above error using a max function. Even then I am not able to see any value for future months.