Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sabilahmed
Resolver I
Resolver I

Complex DAX: Contracted and Uncontracted Revenue - single Row value across multiple months (Help!)

Hello all,

 

HELP ME! 😞

 

I am stuck on a problem that I can't get me head around and need the help of a super Power BI guru.

 

I have an example table below. In my model it is called AgreementAdditions and List:

 

sabilahmed_0-1659452828907.png

 

Below is the relationship model and I have circled where the AgreementAdditions and List table is:

 

sabilahmed_0-1659451312359.png

The Goal is i'm tryiong to acheive the below results in a table visual format (but I need it to work in other visual formats too like bar chart or something like that):

sabilahmed_1-1659452914437.png

 

sabilahmed_2-1659452974800.png

 

I have created the above 2 tables as an example, so as you can see what I want is the Revenue (Ext Price) in the AgreementAdditons and List table to be spread out across multiple months (depending on the start and end date).

 

If there is no end date then the value should continue as contracted. If there is an end date then the value stops in said month, but in the following month continues as Uncontracted.

 

Also I want there to be dynamic filtering available, so if I select FY22-23 or FY23-24, I will see different results (as per above example).

 

So far I have achived the following:

 

sabilahmed_1-1659451950015.png

The table viz above is created as follows:

 

sabilahmed_2-1659452049825.png

 

In the data relationship model you will see a table called: Rev_Type. This is what I use as Rows in the table viz:

 

sabilahmed_3-1659452125152.png

I have yet to add Pipeline, Contracted and Uncontracted_Weighted (80%) in the above table

 

The TOTAL_RS! in values is a DAX calc:

 

TOTAL_RS1 = SUM(Agreement_Billing_Report[Extended Price]) + CALCULATE(SUM('Invoiced Product & Subscription'[Extended Price]),'Invoiced Product & Subscription'[Income Stream] = "RS1 - Contracts") + SUM(Opportunity[Ext Price])

 

Each component of this calc results in a Row in the table viz (i.e. Agreement, SO).

 

SUM(Opportunity[Ext Price] will result in Pipeline row but this has not yet been populated.

 

I am looking to add more components to the above calc so I can eveventually have Contratced, Uncontraced and Uncontracted_Weighted.

 

If you have a better way of doing this I am open to all solutions 🙂

 

Ypur help will be most appreciated and I am always availablle to give you more details to help you so you can help me 🙂

 

Many, many thanks in advance. And good luck!

 

Kind regards,

Sabil

 

😊

 

 

1 ACCEPTED SOLUTION

Hello,

 

This issue has now been resolved. I used a CROSSJOIN between revenue contracts table and Calendar table. I then used SUMMARIZE to group the rows per month per year OR per StartofMonth date.

 

See below:

 

https://community.powerbi.com/t5/Desktop/CROSSJOIN-to-obtain-one-row-per-month-between-start-and-end...

 

Thanks for getting back to me though.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @sabilahmed 

Thanks for reaching out to us.

 Can you share your sample files or sample data that fully covers your needs? Thanks.

 

 

Best Regards,

Community Support Team _Tang

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

Hello,

 

This issue has now been resolved. I used a CROSSJOIN between revenue contracts table and Calendar table. I then used SUMMARIZE to group the rows per month per year OR per StartofMonth date.

 

See below:

 

https://community.powerbi.com/t5/Desktop/CROSSJOIN-to-obtain-one-row-per-month-between-start-and-end...

 

Thanks for getting back to me though.

sabilahmed
Resolver I
Resolver I

@amitchandak Hi Amit, sorry to approach you like this, is it possible you can help me with this? Cheers 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.