cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

HI

I hope someone can help me.

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

Melgaardiversen_1-1594632834423.png

 

 

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 : 

Melgaardiversen_2-1594637320022.png

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

 

6 REPLIES 6
Highlighted
Super User II
Super User II

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

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.


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

Proud to be a Super User!


Thank you,
Antriksh Sharma
Highlighted
Frequent Visitor

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

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

 

Bogføringsdato = Posting date

Melgaardiversen_0-1594640979806.png

 

Melgaardiversen_1-1594641002377.png

 

Best regards 

Rune Iversen

Highlighted
Solution Specialist
Solution Specialist

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

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
Highlighted
Frequent Visitor

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

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.

Highlighted
Advocate I
Advocate I

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

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])))
Highlighted
Frequent Visitor

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

Thx, it worked (y)

BR

Rune

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors