cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexMos Frequent Visitor
Frequent Visitor

Spread revenue across period based on start and end date, slice and dase this using different dates

Hi guys

I have following issues and as new user to PowerBI tried to look for different solutions but neither quite worked / matches my issue (probably because data are located in different tables).

 

So I have bookings (tbl_bookings) with Start and End date. The Amount sits in different table (tbl_amount). I need to spread the amount across differnet time periods (year months, calendar year, financial year, week,etc.) based on the start and end date. And then summarize, filter or view this again based on different columns from different tables. 

 

What i did so far is that I created Date dimensions with date formats I need.  I thought of using the Role Playing approach to come around the one active joint limitations. Any ideas if there may be better way?

 

I tried to create Measure BookingPerMonths but I get following error code (altough there is 1:1 and start,finish date filled for each booking).

BookingsPerMonth = Calculate(SUM(tbl_amount[cur_amount]), FILTER(tbl_amount,COUNTROWS(FILTER(VALUES(DateDim[FulldateAlternateKey]),tbl_booking[dte_start] <= DateDim[FulldateAlternateKey] && tbl_booking[dte_finish] >= DateDim[FulldateAlternateKey] )) > 0))

Error code: A single value for column 'dte_start' in table 'tbl_booking' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

I would love to get something like this matrix as intermediate product which I will group, filter, view again based on eg. customer, date when booking was made, sales rep etc.

 

BookingJul 17Aug 17Sep 17Oct 17Nov 17Dec 17Jan 18Feb 18Mar 18Apr 18May 18Jun 18Jul 18Aug 18Sep 18Oct 18Nov 18Dec 18
2609411,2141,2860000000000000000
2623131,08623,3140000000000000000
231075,02012,72412,31441000000000000000
2489967000000000000000000
2651100010,04510,04510,380000000000000
2651200000010,0609,08610,06032500000000
27864000000023,07425,382000000000

 

 

 These are my tables with tbl_booking and tbl_amount are joint using table tbl_amount_booking  ( uid_booking_amount and then uid_r_booking_amount on uid amount as key) all of these are 1:1  

image.pngimage.pngimage.png

1 ACCEPTED SOLUTION

Accepted Solutions
AlexMos Frequent Visitor
Frequent Visitor

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi Xiaoxin, @v-shex-msft

 

a big thanks for the solution! It works Smiley Happy

 

just to confirm that I really understand your solution, the intermediate table you created actually gives me list of every date when some of the camapign was happening along with id of every campaign that was happening on that particular date. 

 

All this to overcome inability of PowerBi to deal with 1:1, many:may relationships?

 

Thank you!!

Alex

 

8 REPLIES 8
Community Support Team
Community Support Team

Re: Spread revenue across period based on start and end date, slice and dase this using different da

HI @AlexMos,

 

After research on your formula, I found you are try to direct use tbl_booking columns which not be defined. I think it is impossible to direct use undefined and un-summarized columns as filter conditions at measure formula .

 

Can you please share a pbix file for test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
AlexMos Frequent Visitor
Frequent Visitor

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi @v-shex-msft

 

the file has bit truncated data and the tables are missing "tbl_" in name for ease however everything else same.

 

thank you

 

https://1drv.ms/u/s!Ah0PpfTHcxcbg0rw6Bq8Bv9M2lts

 

 

Community Support Team
Community Support Team

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi @AlexMos,

 

After research on your sample file, you can refer to below steps to get summary booking amount.

 

Steps:

1. Use booking table to create a 'booking date range' table to expand all booking uid date range.

booking date range = 
VAR _calendar =
    CALENDAR ( MIN ( booking[dte_start] ), MAX ( booking[dte_finish] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( booking, _calendar ),
            booking[dte_start] <= [Date]
                && booking[dte_finish] >= [Date]
        ),
        "uid_booking", [uid_booking],
        "Date", [Date]
    )

10.PNG

 

2. Remove relationship between booking table and Datedim table.

3. 'Use booking date range' table to link booking table and Datedim table.(uid booking to booking table, date to datedime)

11.PNG

 

4. Use booking[uid booking], datedim[FulldateAlternateKey], amount[cur_amount] to create matrix visual.

12.PNG

 

Notice: 

1. booking table store two date column to describe date range, they only means startdate and end date, so you can't direct use one of them to link calendar table.(your relationship not suitable for your requirement)

2. It seems like your calendar table not exist date range of 2014, so these summarized result are stored in blank column group.

3. I attach modified sample file at below.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
AlexMos Frequent Visitor
Frequent Visitor

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi Xiaoxin, @v-shex-msft

 

a big thanks for the solution! It works Smiley Happy

 

just to confirm that I really understand your solution, the intermediate table you created actually gives me list of every date when some of the camapign was happening along with id of every campaign that was happening on that particular date. 

 

All this to overcome inability of PowerBi to deal with 1:1, many:may relationships?

 

Thank you!!

Alex

 

Super User
Super User

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi,

 

I have solved a similar question here - Distribute projected revenue annually.

 

Hope this helps.

AlexMos Frequent Visitor
Frequent Visitor

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi @v-shex-msft

 

I tried to apply this apply in my original project and it doesnt work (even tough it worked in the sample). I strated from scratch connected just tables I had in sample, keep connection you mention and it still wont work.

in matrix it will always show sum for all periods irrespective what I choose for Columns.

 

any idea?

 

Thank you!

Community Support Team
Community Support Team

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi @AlexMos,

 

Can please share us a sample pbix file to test. I think it will be help.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
AlexMos Frequent Visitor
Frequent Visitor

Re: Spread revenue across period based on start and end date, slice and dase this using different da

Hi @v-shex-msft,

 

I figured out why your solution is not working. There is another table(s) that generate revenue as cur_amount in table Amount. 

Unfortunately I havent found table with relevant bookings (or rather start and end dates for these amounts). Because the database has several tens of tables and is has large volume od data it doesnt seem practical to make sample pbix untill I find the relevant tables responsible for all the amounts in Amount table.

 

however is there way how to bump up your solution - booking data range table with data from another tables?

 

booking date range = 
VAR _calendar =
    CALENDAR ( MIN ( booking[dte_start] ), MAX ( booking[dte_finish] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( booking, _calendar ),
            booking[dte_start] <= [Date]
                && booking[dte_finish] >= [Date]
        ),
        "uid_booking", [uid_booking],
        "Date", [Date]
    )