Forum Discussion

Sadr_Hoda's avatar
Sadr_Hoda
Frequent Visitor
3 years ago

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?

 

Thanks in advance.

2 Replies

  • 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's avatar
      Sadr_Hoda
      Frequent Visitor

      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.