Forum Discussion

tmears's avatar
tmears
Icon for Helper III rankHelper III
8 years ago
Solved

Dax Calcualtion

 

I wonder if anyone could help with this one, i am totally at a loss and not sure if it can be done.  Please bear with me as it is a hard one to explain

 

I have a data table and a table with Sales within Power Bi 

 

I would like a calcualtion that has a total sales figures for the year (i am using fiscal year but that is fine!) however here is the problem.  We sign a contract for 12 months however the orginals sales order equals monthly revenue, so if we win a contract in month 1, there is no order in month 2, so sales for this customer is £100 for the year and not £1200.  I do not want to put £1200 in the first month.

 

I have tried to show below, you can see that we have signed 3 customer in jan, mar and july for £100 per month which equates to £2800

 

 Customer aCustomer bCustomer cTotal
Jan100  100
Feb100  200
Mar100100 400
Apr100100 600
May100100 800
Jun100100 1000
Jul1001001001300
Aug1001001001600
Sep1001001001900
Oct1001001002200
Nov1001001002500
Dec1001001002800
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi tmears,

     

    So you want to know the YTD total price of each customers, right?

    If this is a case, you can try to use below measure to calculate the YTD total.

    Total =
SUMX (
    FILTER (
        ALLSELECTED( Table1 ),
        DATEVALUE ( [Month] & "/1" )
            <= DATEVALUE ( MAX ( [Month] ) & "/1" )
    ),
    [Customer a] + [Customer b]
        + [Customer c]
)

     

    Regards,

    Xiaoxin Sheng

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi tmears,

     

    So you want to know the YTD total price of each customers, right?

    If this is a case, you can try to use below measure to calculate the YTD total.

    Total =
SUMX (
    FILTER (
        ALLSELECTED( Table1 ),
        DATEVALUE ( [Month] & "/1" )
            <= DATEVALUE ( MAX ( [Month] ) & "/1" )
    ),
    [Customer a] + [Customer b]
        + [Customer c]
)

     

    Regards,

    Xiaoxin Sheng