Forum Discussion

Fredrikwahl's avatar
Fredrikwahl
New Member
3 years ago

Calculation without relation to date filters

Hi,

 

I have a problem. I'm tryin to calcutate the profitof a certain product. This containt two calculations;

- Revenue carparts

- Cost carparts 

 

Both of these calculations come from the same source witch is ralated to the the date-table. 

 

In my table I have a column with revenue, next one cost and then profit. The problem is that when i select a certain period, for instance january 2023, i get the revenue from january 2023 and the cost of january 2023. The carparts may be bought several periods before, hence the problem. How can i make a caluclation that says;

 

- Revenue january 2023

- Cost all time 

 

Cost carparts =
CALCULATE(SUM(Hovedbok[Amount]),
            Filter(Hovedbok,Hovedbok[AccountNo]>=4040),
            Filter(Hovedbok,Hovedbok[AccountNo]<=4048))
 
Hovedbok = ledger
 
 

 

 

These are again related to a project-table. 

Need Help

6 Replies

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    I try to understand the problem. Do you mean that you filter a period of time, items in this time frame generate cost and revenue but the items were bought before. ? Sorry i did not understand it. Can you show a good example and also your underlaying data? 

    • Fredrikwahl's avatar
      Fredrikwahl
      New Member
      3 years ago

      Hi!

       

      The calculation that shows revenue carparts is as follows;

      Salg deler =

      //Inntekter
      VAR P1 = CALCULATE(SUM(hovedbok[Amount]),
                  Filter(Hovedbok,Hovedbok[AccountNo]=3001))

      Var P2 = CALCULATE(SUM(Hovedbok[Amount]),
                  FILTER(Hovedbok,Hovedbok[AccountNo]=3004))

      Return
      -(P1)-(P2)
       
      The calculation that shows cost carparts is as above. 
       
      I have serveral slicers, years and months as shown above in the picture. 
       
      The problem is that I want to show the profit on these projects over a period selected from the slicer. That means that i want to show the revenue carparts, for example january 2023. But when i choose that period I also get the cost from january 2023 aswell, and the cost may originate from for example desember 2022. That means that the cost-calcuted has to be independent from the slicer. 
    • Fredrikwahl's avatar
      Fredrikwahl
      New Member
      3 years ago

      In our business we order the carparts serveral periods before we invoice the part.

  • Padycosmos's avatar
    Padycosmos
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Please try the following:

    Cost carparts =
    CALCULATE(SUM(Hovedbok[Amount]),
                Filter(Hovedbok,Hovedbok[AccountNo]>=4040 && Hovedbok[AccountNo]<=4048))
    • andhiii079845's avatar
      andhiii079845
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Okay, you can filter your measures via date table:

      - Revenue january 2023

      - Cost all time 

      But the third measure do not use the filter, because of the missing relationship?