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
robg89
Regular Visitor

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?

 

robg89_0-1680446349384.png

 

TIA

 

7 REPLIES 7
andhiii079845
Super User
Super User

Thank you, i build a easier small data model and it works: (YYYY - MM, comes from the date table)

Bildschirm­foto 2023-04-02 um 18.19.15.png

andhiii079845_0-1680452244875.png

 

Bildschirm­foto 2023-04-02 um 18.16.44.png

 

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: 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




andhiii079845
Super User
Super User

Please show some screenshot of your datamodel, the visual (involved columns from the tables)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




robg89_0-1680447712457.png

 

robg89_1-1680447750362.png


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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




robg89_0-1680450034500.png

robg89_2-1680450168789.png

 

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.

andhiii079845
Super User
Super User

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]))





Did I answer your question? Mark my post as a solution!

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?

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.

Top Solution Authors