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
Anonymous
Not applicable

New and Renewal totals per month based on month

Hi All,

 

I have 3 tables: a Payments table, My BI Calendar table and a Service Table.

 

Service is joined to the Payments table (via a Service ID) and my Payments table is joined to my Calendar table (via Payment Date).

 

In the Service table I have a field for 'Purchase Date' - which is the date when the service was first purchased (The services are typically renewed on a period basis)

 

I currently have a meaure for summing my Payment table based on various filters in my Service Table. What I need is 2 measures (the logic should be much the same, one being the inverse of the other) whereby I am summing the Payments in the payment table, based on if the Year and Month for the Purchase Date in the Service table. If it matches the Month of the Payment Date - it's counted as New, if it doesn't it will be counted in the renewal measure

 

e.g. New_Services = calculate(
sum([Payment Amount]), filter(Service_Table,Service_table[Purchase Date])

)

 

But I know the above isn't right.

5 REPLIES 5
v-xulin-mstf
Community Support
Community Support

Hi @Anonymous

 

I got you, could you provide sample data without sensitive information?

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Best Regards,

Link

amitchandak
Super User
Super User

@Anonymous , In power bi, we prefer Star Schema, It means Service and payment should join in power bi.

 

If needed, you can merge tables and have a table Servicepayment with all needed keys and others can be either service or payment.

 

Join both with date and common dimension, but not with each other.

 

refer this video - were i dicussed about independent facts -https://youtu.be/kXls79iNPqc?list=PLPaNVDMhUXGb16ivjoDbhjX54b8TGRjOF

 

refer: https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

Anonymous
Not applicable

Er, it is a Star Schema?

Payment is my Fact, with Service joined to Payment as a Dimension

and Date joined to payment as a Dimension.

@Anonymous , Based on what I got.Check the two ways

 

You want to combine data based on the payment date and service date on the same visual. For example in month of March 2021, How much payment and how much services are done.

In that case, payment has to act fact too.

You can have one more copy of the payment table join with the date and payment(dimension) and use this fact with date to show both payment and service data together

 

Another way is this formula. Do not join payment with date

Try a measure like

sumx(filter(values(payment[payment Date]), payment[payment Date] =max(Date[Date])), calculate(sum(payment[payment Amount])))

Anonymous
Not applicable

There's nothing in that measure though that references the purchase date/service table though?

 

The end-goal is to have a bar graph showing for all the payments made in a month, how much was new services and how much was renewals.

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.