Cumulative Value Measure not to be effected by date range in x axis

[Majad_Chowdhury]

Hi guys,

Need a hand. 

Just to set the picture. I have a dataset which holds the fields, Customer, Created Date, Amount. It holds data of customers created from 2023 to now.

I have a line visual which shows date as the x axis and a cumulative amount on the Y axis.

I have created a measure to calculate the cumulative amount off the "Amount" column which gives a monthly amount.

The measure used to calculate the cumulative value works correctly when there is no created date filter in the visual, however the problem is when I change the dates visible in the x axis (to show only 2025), the cumulative measure does not take into account any of the values before the visible dates. 

The cumulative measure is created with the following:

Amount_Cumulative = CALCULATE(
    SUM('Dataset'[Amount]),
    FILTER(
        ALLSELECTED('Dataset'),
        'Dataset'[CreatedDate] <= MAX('Dataset'[CreatedDate])
    )
)

[lbendlin · Accepted solution]

Replace ALLSELECTED('Dataset') with ALL('Dataset'[CreatedDate])

[v-pagayam-msft]

Hi @Majad_Chowdhury ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Thank you.

[v-pagayam-msft]

Hi @Majad_Chowdhury ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[v-pagayam-msft]

Hi @Majad_Chowdhury ,
Thank you @Ashish_Mathur  and @lbendlin for the prompt response!

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar table
3. To your visual/filter/slicer, drag Year and Month name from the Calendar table
4. Write these measures

Total = sum('Dataset'[amount])

Total since inception = calculate([Total],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

[lbendlin · Accepted solution]

Replace ALLSELECTED('Dataset') with ALL('Dataset'[CreatedDate])