Sum of values where max id is less than dynamic date

[CarlBlunck]

Hi everyone,

I am struggling to develop a measure that sums the values where the max id of the value is less than a dynamic date.  Let me explain.  I have the following data set:

invoiceid	Simpro jobid	dateinvoiceissued	WorkInProgress
8182	3784.AU	8/02/2022	$222,588.00
8548	3784.AU	27/04/2022	$185,088.00
8561	3784.AU	29/04/2022	$110,088.00
9557	3784.AU	2/11/2022	$35,088.00
8467	3908.AU	4/04/2022	$243,438.24
8468	3908.AU	4/04/2022	$191,039.68
8818	3908.AU	17/06/2022	$113,632.94

I have the dateinvoiceissued connected to a MasterCalendar and I need to be able to sum the WorkInProgress values based on the max (invoiceid) for each Simpro jobid where the dateinvoiceissued is less than the date chosen from the MasterCalendar date.

Expected outputs based on a date chosen of:

- 10/02/2022 = $222,588

- 05/04/2022 = $413,627.68

- 29/04/2022 = $310.127.68

- 17/06/2022 = $223.720.94

- 03/11/2022 = $148,720.94

Appreciate the help in advance!

Thanks

Carl

[Anonymous]

Hi @CarlBlunck ,

I could not access this url: https://drive.google.com/file/d/1n4Jbbml3_nw4UmdByPYTeOD2bhPiJ2B1/view?usp=sharing , please reshare.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu

[CarlBlunck]

Hi @Anonymous 

Try this link - https://drive.google.com/file/d/1n4Jbbml3_nw4UmdByPYTeOD2bhPiJ2B1/view?usp=drive_link

Cheers

Carl

[Anonymous]

Hi @CarlBlunck ,

Sorry, i don't have google account, so couldn't access it.

Best regards,
Community Support Team_Binbin Yu

[TobyNye]

I made a sample data set with the small table that you provided and I believe the following suits your needs:

The measure works by effectively creating a table with unique Simpro Job ID's, finding the max invoice date per row and the work in progress value in that row, it then sums each row of the table, the MAX at the end is just to return only the specific value in the table reference, more examples:

The only thing to be wary of is that the date filter is on or before and not just before, I'm using the date column in the table but if you have your main calendar table linked, just use that one in the slicer instead and you should be all fine, raw measure code:

Test =

VAR _table = SUMMARIZE('Fact Table', 'Fact Table'[Simpro Job ID], "invoice id", MAX('Fact Table'[Invoiceid]), "WiP", CALCULATE(SELECTEDVALUE('Fact Table'[Work in Progress]), 'Fact Table'[Invoiceid] = MAX('Fact Table'[Invoiceid])))

RETURN

SUMX(_table, MAX([WiP]))

Hope this helps, let me know if you have any issues/questions

[CarlBlunck]

Hey @TobyNye - after further validation this didn't work out so well.  I read this article about summarize All the secrets of SUMMARIZE - SQLBI and I think the issue is being cause by all of the other columns in my data set.  I am trying other methods out, but if you have an alternative, would be good to see it.  Here is the PBIX file I am trying to get this working in - https://drive.google.com/file/d/1n4Jbbml3_nw4UmdByPYTeOD2bhPiJ2B1/view?usp=sharing 

[CarlBlunck]

That's awesome mate, thank you!! Really appreciate you taking the time to help 🙂