Dax formular which include all job ledger entry based on a finished date

[Anonymous]

HI

I hope someone can help me.

I am trying to make a profitabillity analysis of jobs/projects with data from dynamics NAV: 

I have a year slicer (Dimdate table), but the sale amounts and cost amounts of a job must first be included in the report, when the finished date is in the year, we have sliced to. Example: we have a job/project which streches over a 2 year period (som costs and sales are posted in 2018 and some cost and sales are posted in 2019), and the finished date (DimJob table) is in 2019 and therefor should all the job/projects cost and sales (2018 and 2019) be shown, when I slice to year 2019. 

I have tried : 

Afsluttet dato = finished date
Kostbeløb = cost amount

I can't find the dates when I just use "IF", therefor I need MAX?? 

I have made a new coloumn in data, where i have lookupvalue on finished date from dimjob table to factjob table. 

I appreciate this community and hope someone can help me!

Best regards

Rune

[foomanschu13]

Can you utilize a contract to date or inception to date formula? This would remove the filter context and introduce one that does not factor in the year slicer.  The visual could still be filtered by the finish date.  

CALCULATE([Costs],FILTER(ALL('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])))

[Anonymous]

Thx, it worked (y)

BR

Rune

[Anonymous]

I just wonder what you would like to see if you slice by one month, say, 2019-Jun. As it stands right now, it's not completely clear what you want the calculation to return under at least some conditions. I suspect you'd like something like a "running total" for any one project... but that's a guess only.

Bset
D

[Anonymous]

I will not slice bye month, only year. I want the dax to includ all the costs and sales if the project has a finished date between 01-01-2020 and 31-12-2020. Some of the cost can be posted the year before and must be included when the projects finished date is in sliced year.

[AntrikshSharma]

first remove the dates from the MAX function and move them outside MAX example MAX () <= Dates and then remove the CALCULATE before MAX, that's not doing anything.

[Anonymous]

Thanks, but now the dax function return 0 for every job, so it do not solve it all.

Bogføringsdato = Posting date

Best regards 

Rune Iversen