Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Life to Date Calculation and Monthly calculation

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

 

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , 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])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        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.

  • slev1111n's avatar
    slev1111n
    New Member
    2 years ago

    I've always been skeptical of calculators, especially those dealing with fate and such. Still, I haven't found any good calculators that explain what everything means. The last one I used gave information on your lessons & challenges, strengths and weaknesses, explaining their influence on life and actions. At the very least, it's interesting.