Forum Discussion
SUM measure from different period
Hello,
For the below data I am trying to calculate the sum of the sale amount where the sale is not in the reporting period but the service start date is.
I keep trying to play around with the SUM formula but I cannot get it to work! Can somebody please help?
TIA
7 Replies
andhiii079845Solution Sage
You want to use service starts date but the activ relationsship is via sales closed? Create a inactive relationship between service starts and the dim table.
Than you can do somethink like MEASURE = calculate(sumx(sales,sales[sales amount]),userelationsship(dimtable[date],sales[servicestarts]))
I have tried this, the inactive relationship is set up but your formula just gives me sales if service starts in the same month. I need to get the number where service starts in the month BUT the sale closed date (active relationship) is not the same period?
- andhiii079845
Please show some screenshot of your datamodel, the visual (involved columns from the tables)
The first is the visual and the second is the updated formula.- andhiii079845
I do not see the mistake. perhaps show some sample data (from both tables) and which result you expected.
- andhiii079845
Thank you, i build a easier small data model and it works: (YYYY - MM, comes from the date table)
The measure:Sales close date = CALCULATE(sumx(Sale,Sale[Sale]),USERELATIONSHIP('Date'[Date],Sale[Close]))Perhaps try to transfer your problem to this easier model as a example.
The download:
