Need True JTD and YTD Revenue Amount and Labor Cost Amount from Dynamics 365 F&O

[nfowler_PDR]

Hello,

Please be gentle! 

I am creatinge a Production Report that will display all active projects with their Contract Value, Invoiced Amount, Invoiced JTD/YTD, Revenue Amount, Revenue JTD/YTD, Revenue to be Earned Amount, Revenue to be Billed Amount, and Labor cost. 

I have 3 Problems.

1.

Problem: When using the [DateFinancial_Date] slicer, it is not gathering the correct values. For some reason it is not grouping all completed invoices into the selected date range because it is only taking the invoices closed within that range. 

Example: I have an invoice completed in May, but it was closed in June (with the clompleted date of the invoice dated for May) by Accounting. This invoice will not be included in the report unless I include the June dates in the slicer to include that invoice. But, that does not help because other invoices will have been closed in that time as well which will change all of the values in the matrix.

Question: How do I get all completed invoices to dispaly in the correct time period regardless of when it was closed by Accounting? 

2.

Problem: When trying to get Invoiced, Revenue, and Labor Cost JTD values, the values will change depanding on the selected date range slicer (this slicer is included to narrow down to all active projects in a specific period). 

Example: I want to see how much fee we have invoiced from March 1, 2022 to February 28th 2023 in the date slicer. Next to the Invoiced Amount based on the date, I want to see the Invoiced Amount JTD (From the start of the project to now) and not just from the selected date range. 

Question: Is there a way to make the JTD values stay the same regardless of the date slicer being changed?

Measure: This is the measure I have created for Labor Cost JTD. I think this is still using the first date of the date slicer to generate the values because the number will change is I go back in time, but not forward. 

3.

Problem: Same as above, but for YTD. 

Measure: This is the measure I have created so far, but it fails to display anything. 

Any help would be appreciated!

I will be online today and tomorrow to aid with more info if someone has a chance to take a look. 

Thank you! 

[v-yanjiang-msft]

Hi @nfowler_PDR ,

Please see if REMOVEFILTERS function solve your problem.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nfowler_PDR]

Hello @v-yanjiang-msft,

Thanks for taking the time to take a look at my issue. 

For the REMOVEFILTERS function, where and how would I integrate that to the code for:

Labor Cost _ Date Financial JTD =

CALCULATE (

    SUM ( FACTActual[ActualAmount_Labor_] ),

    FILTER (

        ALL ( DateFinancial ),

        DateFinancial[DateFinancial_Date] <= MAX ( DateFinancial[DateFinancial_Date] )

    )

)

Or:

Labor Cost _ Date Financial YTD =

CALCULATE (

    SUM ( FACTActual[ActualAmount_Labor_] ),

    FILTER (

        ALL ( DateFinancial ),

        [DateFinancial_Date]

            >= DATE ( YEAR ( MAX ( DateFinancial[DateFinancial_Date] ) ), 1, 1 )

    ),

    FILTER (

        ALL ( DateFinancial ),

        [DateFinancial_Date] <= MAX ( DateFinancial[DateFinancial_Date] )

    )

)

?

Thank you again for your time.

-Nat