cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## How to do cumulative sum with conditional First date

Hello,

I need help on how to calculate the accumulative sales for FYTD (Fiscal year to date). Fiscal year start on July each year.

So after July, the FYTD sale will be accumulative sum. My data looks something like below and I need to calculate Column C. Any suggestion?

 A B C Date Sales FYTD Sales Jan-17 10 =10 Feb-17 20 =10+20 Mar-17 30 =10+20+30 Apr-17 40 =10+20+30+40 May-17 50 =10+20+30+40+50 Jun-17 60 =10+20+30+40+50+60 Jul-17 70 =70 Aug-17 80 =70+80 Sep-17 90 =70+80+90 Oct-17 100 =70+80+90+100 Nov-17 110 =70+80+90+100+110 Dec-17 120 =70+80+90+100+110+120 Jan-18 130 =70+80+90+100+110+120+130 Feb-18 140 =70+80+90+100+110+120+130+140 Mar-18 150 =70+80+90+100+110+120+130+140+150 Apr-18 160 =70+80+90+100+110+120+130+140+150+160 May-18 170 =70+80+90+100+110+120+130+140+150+160+170 Jun-18 180 =70+80+90+100+110+120+130+140+150+160+170+180 Jul-18 190 =190 Aug-18 200 =190+200

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @jinny_le ,

Please create a fiscal year and then create a measure to calculate the running total for YTD based on fiscal year.

Calculated Columns:

FY = IF('Sample'[Date].[MonthNo]>6,'Sample'[Date].[Year]+1,'Sample'[Date].[Year])
Measure:
Running Total = CALCULATE(
SUM('Sample'[Sales]),
FILTER(ALL('Sample'),'Sample'[Date]<=MAX('Sample'[Date]))
,VALUES('Sample'[FY])
)

Output:

Best Regards,
Mail2inba4

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

Hi @jinny_le ,

Please create a fiscal year and then create a measure to calculate the running total for YTD based on fiscal year.

Calculated Columns:

FY = IF('Sample'[Date].[MonthNo]>6,'Sample'[Date].[Year]+1,'Sample'[Date].[Year])
Measure:
Running Total = CALCULATE(
SUM('Sample'[Sales]),
FILTER(ALL('Sample'),'Sample'[Date]<=MAX('Sample'[Date]))
,VALUES('Sample'[FY])
)

Output:

Best Regards,
Mail2inba4

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

Fantastic! Thank you 🙂

Super User

@jinny_le ,
Try this: x is my table.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

``FYTD = TOTALYTD(sum(x[Sales ]),x[Date],ALL(x),"6/31")``

Proud to be a Super User!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors