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-yanjiang-msft]

Hi @Sadr_Hoda ,

Is [@*FCY] a measure? How do you know it's not picking any data, have you write the formula out seperately to see? Could it be because the measure [@*FCY] has nothing to do with table NL_Structure-BS?

Best Regards,
Community Support Team _ kalyj

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

[Sadr_Hoda]

Yes [@*FCY] is a measure, use to create P&L for Fiscal current year. I have dobule check it but not showing any result. Moreover NL_Strucutre_BS is a mapping table which is link with Trial balance table.

Do you know any other way to create forecasting P&L, BS and CF?

Please let me know, You can reach me @ 00974 55745337.

Thanks in advance.