cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Cumulative total for month

Hi All,

I have run into a problem I cannot resolve. I have set of dates, Target and Actual numbers in a table. I need to calculate the cumulative total for each day of the month. The logic appears to be simple enough, but it doesn't work. Here is the data:

DATE            TARGET ACTUAL RT_mnth st_day_mnth  last_day_mnth

 1/1/2023 27 38 38 1/1/2023 1/31/2023 1/2/2023 39 20 20 1/1/2023 1/31/2023 1/3/2023 26 37 37 1/1/2023 1/31/2023 1/4/2023 36 24 24 1/1/2023 1/31/2023 1/5/2023 18 23 23 1/1/2023 1/31/2023 1/6/2023 23 19 19 1/1/2023 1/31/2023 1/7/2023 14 23 23 1/1/2023 1/31/2023 1/8/2023 36 10 10 1/1/2023 1/31/2023 1/9/2023 14 40 40 1/1/2023 1/31/2023 1/10/2023 10 12 12 1/1/2023 1/31/2023 1/11/2023 36 25 25 1/1/2023 1/31/2023 1/12/2023 20 20 20 1/1/2023 1/31/2023 1/13/2023 10 37 37 1/1/2023 1/31/2023 1/14/2023 36 10 10 1/1/2023 1/31/2023 1/15/2023 40 29 29 1/1/2023 1/31/2023

'st_day_mnth' and 'last_day_mnth' are calculate measures from DATE. 'RT_mnth' is the cumulative total of ACTUAL column and has the following expression:

RT_mnth = CALCULATE(
SUM(TestSh[ACTUAL]), FILTER(TestSh,
TestSh[DATE]>= [st_day_mnth] && TestSh[DATE] <= [last_day_mnth])
)
I tried ALL and ALLEXCEPT functions but it does not give me the desired result. I have also tried SUMX with the table name and the column ACTUAL, but there is no change.
Please suggest a suitable change to my code so that I get the cumulative total up to the date in the DATE column for each month. One solution is to change this to a date table and apply DATEMTD. But I want to understand why it is not working.
Please suggest where I am going wrong.

1 ACCEPTED SOLUTION
Super User

@kkanda

Use this measure:

``````RT_mnth =
VAR __StartDate =
EOMONTH ( MAX ( TestSh[DATE] ), -1 ) + 1
VAR __LastDate =
MAX ( TestSh[DATE] )
RETURN
CALCULATE (
SUM ( TestSh[ACTUAL] ),
TestSh[DATE] >= __StartDate,
TestSh[DATE] <= __LastDate
)``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
2 REPLIES 2
Resolver I

Thank you @Fowmy... it worked. I tried STARTOFMONTH function for __startDate. That has worked also.

Super User

@kkanda

Use this measure:

``````RT_mnth =
VAR __StartDate =
EOMONTH ( MAX ( TestSh[DATE] ), -1 ) + 1
VAR __LastDate =
MAX ( TestSh[DATE] )
RETURN
CALCULATE (
SUM ( TestSh[ACTUAL] ),
TestSh[DATE] >= __StartDate,
TestSh[DATE] <= __LastDate
)``````
Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors