Life to Date Calculation and Monthly calculation

[NAV21N]

Hi All,

I have a question on Life to date calculation and monthly calculation. We need to calculate the Life to date values for a project right from the start date to final date of a transaction being posted. I have a dataset called D365PromoCampaigns that has project codes in it. Unfortunately my dataset is a view that resides on a SQL Azure database otherwise and has customer data in it so i can't share the dataset.
This is how my table looks like now. I need to calculate Life to date (LTD) and Monthly amounts what we call as Monthly amounts. 

D365PromoCampaigns
Fields in the dataset include the following:

1. Project ID

2. Amount called as TransactionCurrencyAmount

3. Date filter is on Accounting date that tracks the date on which the various transactions on a project are performed across multiple years.

4. Chart of accounts (MainAccount) 

Relationships:

Added a relationship between D365PromoCampaigns (AccountingDate) and CalendarTable (Date)

Life to Date Calculation

Scenario 1:
On using the Accounting Date:

My Life to date calculation looks like this. I wanted to know if i remove the monthly filters why does the LTD calculation go away? (Understand that the filter is on the Accounting Date) 

Output:

Without the Month columns:

Question:

Now, when i remove months from the matrix visual the LTD calculation is same as the monthly calculation which is incorrect. Wanted to know where i am going wrong with the LTD calculation?

Scenario 2:

On using the Calendar Date:

Also, when i try to use the Calendar table date the LTD value goes blank (On Using the Calendar Table). 

 LTD Calculation with Calendar[Date].

Output:

Last question is Life to date appears to be the same as the Monthly calculation? How do i fix that up?

Thanks,

Navneeth Nagrajan

Technical Consultant - Dynamics 365/Power Platform

[amitchandak]

@NAV21N , have tried allexcept on project or all on date

calculate(sum(Table[transaction currency Amount]) , allexcept(Table[project]))

or

calculate(sum(Table[transaction currency Amount]) , all(Table[date]))

or

calculate(sum(Table[transaction currency Amount]) , removefilters(Table[date]))

or cumm if you need till date

Cumm Sales = CALCULATE(SUM(Table[transaction currency Amount]),filter(allselected(date),date[date] <=max(date[Date])))

[NAV21N]

@amitchandak: Thanks. All the expressions above lead to the below mentioned result. Its showing the same value as the Monthly calculations.

Output:

When i remove the month from the visual it shows the same value for Life time to date and monthly. For example, for monthly it should show the breakdown as expected but for the life time to date it should keep adding the values which it fails to do.

[v-kkf-msft]

Hi @NAV21N ,

Mark the Calendar table as date table.

Then create the following measure:

Actual LTD Val = 
CALCULATE(
    SUM(D365PromoCampaigns[TRANSACTIONCURRENCYAMOUNT]),
    ALLEXCEPT(D365PromoCampaigns,D365PromoCampaigns[PROJECT],D365PromoCampaigns[code]),
    DATESBETWEEN(
        'Calendar'[Date],
        BLANK(),
        MAX(D365PromoCampaigns[Accounting Date])
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[NAV21N]

Hello @v-kkf-msft,
Many thanks for the response. Last two question, if we remove the YearMonth Column (as highlighted in red) from the below image, is it possible to calculate LTD and Monthly amount value for those codes?

How do we achieve the LTD and monthly values without the Year Month date added to the visual?

Thanks.

[v-kkf-msft]

Hi @NAV21N ,

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @NAV21N ,

If you remove the YearMonth column, it will only show a summary value for all months, like this:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[NAV21N]

Hello @v-kkf-msft,

Understand with Month and Year in there its easy to achieve this. 

Well, that doesn't solve the problem. We need the Life to Date calculation trick to show up the entire Project lifecycle total (ever since the project exists till date).