Calculate revenue base on Start and End Date price

[abhishekrws01]

Hello,

I am looking for some help. 

I have the following two tables:

1. Unit - No of units sold by day

2. Price - Price per unit with Start and End date

I want to calculate the revenue by creating/summarizing a table called "OutPut", where I can see the Unit, Price and Revenue by day.

Please help if possible.

Thanks

[kpost · Accepted solution]

No idea if this is the best way BUT it absolutely would work:

In Power Query, create a table that looks like this, using the starting and ending values from your original table shown in your question.

 

Here's the .pbix file with the steps :  https://files.catbox.moe/ye4rep.pbix

but most of the 'magic' is this custom column that generates all the dates between the ending and starting dates:

let
Dates = {Number.From([Start Date]) .. Number.From([End Date])},
As_Date =
List.Transform(Dates, each Date.From(_))
in
As_Date

then you just expand the column and voila you have a table of prices by date.

Then once you have this, you basically have a nice lookup table of price by date.  So create a connection between this table and your other tables in such a way that you can use a dax formula like

CALCULATE (SUMX(price * units), RELATEDTABLE(  etc etc   )

or something like that....

//Mediocre Power BI Advice,  but it's free//

[abhishekrws01]

Thank you.. This one also works. I guess it will take more space in the model.

[v-yilong-msft · Accepted solution]

Hi @abhishekrws01 ,

I create two tables as you mentioned.

Then I create a measure named Price  in Unit_Table.

Price =
CALCULATE (
    MAX ( 'Price_table'[Price] ),
    FILTER (
        'Price_table',
        SELECTEDVALUE ( 'Unit_Table'[Date] ) >= 'Price_table'[Start_Date]
            && SELECTEDVALUE ( 'Unit_Table'[Date] ) <= 'Price_table'[End_Date]
    )
)

Then I create another measure named Revenue.

Revenue = SUM( Unit_Table[Units]) * 'Unit_Table'[Price]

Finally you will get the OutPut you want.

Best Regards

Yilong Zhou

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

[abhishekrws01]

Thank you very much.. it works

[kpost · Accepted solution]

No idea if this is the best way BUT it absolutely would work:

In Power Query, create a table that looks like this, using the starting and ending values from your original table shown in your question.

 

Here's the .pbix file with the steps :  https://files.catbox.moe/ye4rep.pbix

but most of the 'magic' is this custom column that generates all the dates between the ending and starting dates:

let
Dates = {Number.From([Start Date]) .. Number.From([End Date])},
As_Date =
List.Transform(Dates, each Date.From(_))
in
As_Date

then you just expand the column and voila you have a table of prices by date.

Then once you have this, you basically have a nice lookup table of price by date.  So create a connection between this table and your other tables in such a way that you can use a dax formula like

CALCULATE (SUMX(price * units), RELATEDTABLE(  etc etc   )

or something like that....

//Mediocre Power BI Advice,  but it's free//