topic Re: SUM measure from different period in DAX Commands and Tips

SUM measure from different period

robg89 — Sun, 02 Apr 2023 14:40:13 GMT

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

Re: SUM measure from different period

andhiii079845 — Sun, 02 Apr 2023 14:46:13 GMT

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

Re: SUM measure from different period

robg89 — Sun, 02 Apr 2023 14:53:50 GMT

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?

Re: SUM measure from different period

andhiii079845 — Sun, 02 Apr 2023 14:58:54 GMT

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

Re: SUM measure from different period

robg89 — Sun, 02 Apr 2023 15:03:23 GMT

The first is the visual and the second is the updated formula.

Re: SUM measure from different period

andhiii079845 — Sun, 02 Apr 2023 15:26:54 GMT

I do not see the mistake. perhaps show some sample data (from both tables) and which result you expected.

Re: SUM measure from different period

robg89 — Sun, 02 Apr 2023 15:44:08 GMT

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.

Re: SUM measure from different period

andhiii079845 — Sun, 02 Apr 2023 16:21:37 GMT

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:

SUM measure from different period.pbix