UNABLE TO PICK DATA FROM MEASURE

[Sadr_Hoda]

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.

[v-rongtiep-msft]

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.

[Sadr_Hoda]

@v-rongtiep-msft

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.

[Sadr_Hoda]

@v-rongtiep-msft 

Any suggestion from your side?