cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## UNABLE TO PICK DATA FROM MEASURE

Hi,

I am preparing a forecasting P&L, BS and SOCF position, however, I am stuck in BS.

For each forecasted year, I have created a "measure" which is based on prior year data multiple by growth rate in case of P&L. Find below query in short which is working perfectly.

#FCY1 PL =
VAR Sales = CALCULATE([#FCY1 - Ave],PL_Structure[Reporting]= "Sales",ALL())*((SELECTEDVALUE('Growth Sales%'[Growth %])/100)+1)
Var  Cost_Sale = CALCULATE([#FCY1 - Ave],PL_Structure[Reporting]= "Cost of sales",ALL())*((SELECTEDVALUE('Growth COGS %'[Growth %])/100)+1)
VAR Gross_profit = Sales + Cost_Sale
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE(PL_Structure[Reporting]) = "Sales", Sales,
SELECTEDVALUE(PL_Structure[Reporting]) = "Cost of sales", Cost_Sale,
SELECTEDVALUE(PL_Structure[Reporting]) = "Gross profit", Gross_profit,
SELECTEDVALUE(PL_Structure[Reporting]) = "Net profit/(loss)",Net_profit
)

Now, I have created another query for BS, which is taking data from previous year balance sheet "Measure" plus current year P&L measure. Below is query

#FCY1 BS =

VAR Sales = CALCULATE([#FCY1 PL],PL_Structure[Reporting]= "Sales")

Var Cost_Sale = CALCULATE([#FCY1 PL],PL_Structure[Reporting]= "Cost of sales")

VAR PY  = CALCULATE([#FCY BS])

VAR AR = Sales/365*SELECTEDVALUE('Days - A/c Receivable'[Days])

VAR NR = Sales/365*SELECTEDVALUE('Days - N/R'[Days])

VAR Inventories = -Cost_Sale/365*SELECTEDVALUE('Days - Inventory'[Days])

VAR AP = -(Inventories-Cost_Sale-CALCULATE([@*FCY], 'NL_Structure-BS'[Reporting Category] = "Inventories")/365*SELECTEDVALUE('Days - A/P'[Days])

RETURN

SWITCH(

TRUE(),

SELECTEDVALUE('NL_Structure-BS'[Reporting Category]) = "Accounts receivable",AR,

SELECTEDVALUE('NL_Structure-BS'[Reporting Category]) = "Notes receivables - current",NR,

SELECTEDVALUE('NL_Structure-BS'[Reporting Category]) = "Inventories",Inventories,

SELECTEDVALUE('NL_Structure-BS'[Reporting Category]) = "Accounts payable", AP

)

Issue:-

I have notice that, this DAX function is not picking any data (Account payable query:

-CALCULATE([@*FCY], 'NL_Structure-BS'[Reporting Category] = "Inventories"

Can anyone help me how to bring open balance from one Measure into another measure?

3 REPLIES 3
Anonymous
Not applicable

Could you please provide a pbix file without privacy information and desired output with more details? It seems there are more than onw table. What are the relationships between the tables?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

@Anonymous

Hi V-Polly please find the attached necessary details for your reference.

Snap shot for table with relationship for your reference.

Measure [#FCY1 PL] reading data from mearure [#FCY1 - Ave] and table PL Struture. Measure [#FCY1 - Ave] is created on table TB and Budget.

Formula ;#FCY1] is [*Completed current period]+[*Pending current period]

*Completed current period =
CALCULATE([Total current year consol.], 'GL - Transaction'[Year] = 2022,'GL - Transaction'[Month]=1,GL_Mapping[Year]=2022) +
CALCULATE([Total current year consol.], 'GL - Transaction'[Year] = 2022,'GL - Transaction'[Month]=2,GL_Mapping[Year]=2022) + so on

Completed current period is actual result of close period

*Pending current period =
CALCULATE([Total budget consol.],Budget[Month] = 11,Budget[Year] = 2022,GL_Mapping[Year]=2022)
+
CALCULATE([Total budget consol.],Budget[Month] = 12,Budget[Year] = 2022,GL_Mapping[Year]=2022)
+ so on

Pending current period is the budgeted result of remainining period in a year

Measure #FCY1 BS  is created on above measure and table NL Strucutre-BS.

Frequent Visitor

@Anonymous

Any suggestion from your side?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors