Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Thank you, i build a easier small data model and it works: (YYYY - MM, comes from the date table)
Perhaps try to transfer your problem to this easier model as a example.
The download:
Proud to be a Super User!
Please show some screenshot of your datamodel, the visual (involved columns from the tables)
Proud to be a Super User!
The first is the visual and the second is the updated formula.
I do not see the mistake. perhaps show some sample data (from both tables) and which result you expected.
Proud to be a Super User!
The first table is the dimdate table and the second is the opportunity data. I would expect the calculation to come to 5,893.74 for January. As this relates to sales starting in January but not closed in January.
The close date has an active relationship to the Date column.
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]))
Proud to be a Super User!
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?
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |