Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
@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/
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])))
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |