topic UNABLE TO PICK DATA FROM MEASURE in DAX Commands and Tips

UNABLE TO PICK DATA FROM MEASURE

Sadr_Hoda — Wed, 02 Nov 2022 07:08:52 GMT

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?

Thanks in advance.

Re: UNABLE TO PICK DATA FROM MEASURE

Anonymous — Thu, 03 Nov 2022 03:05:02 GMT

Hi Sadr_Hoda ,

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?

How to Get Your Question Answered Quickly

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.

Re: UNABLE TO PICK DATA FROM MEASURE

Sadr_Hoda — Sun, 06 Nov 2022 12:51:17 GMT

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.

Re: UNABLE TO PICK DATA FROM MEASURE

Sadr_Hoda — Wed, 09 Nov 2022 13:57:38 GMT

Anonymous

Any suggestion from your side?