Cumulative total by month beginning in April

[alysonchu]

I've been rolling happily along with a measure that gave me a cumulative total by month since April. But ever since January, something is off! I'm sure it must be something to do with the fact that I am working with a April-Mar year instead of Jan-Dec.

Here's the measure as it is now (where there is a relationship between the Calendar and Expenses tables based on the Date field:

kgCO2e (Cumulative) = 
CALCULATE(
   SUM('Expenses'[kgCO2e]),
   FILTER(
      CALCULATETABLE(
         SUMMARIZE('Calendar', 'Calendar'[Date].[MonthNo], 'Calendar'[Date].[Month]),
         ALLSELECTED('Calendar')
      ),
      ISONORAFTER(
         'Calendar'[Date].[MonthNo], MAX('Calendar'[Date].[MonthNo]), DESC,
         'Calendar'[Date].[Month], MAX('Calendar'[Date].[Month]), DESC
      )
   )
)

And this is what the visual looked like up until Dec.

However, with the inclusion of dates up to Feb 2026, the visual now looks like this:

I've tried adding a column to the Calendar table called Adjusted Month No = 

IF('Calendar'[Month]>3,'Calendar'[Month]-3,'Calendar'[Month]+9). And then adjusting the measure as below, but that's not worked either, it just gives me the total per month, not the cumulative sum: 

kgCO2e (Cumulative) v2 = 
CALCULATE(
   SUM('Expenses'[kgCO2e]),
   FILTER(
      CALCULATETABLE(
         SUMMARIZE('Calendar', 'Calendar'[Adjusted Month No], 'Calendar'[Date].[Month]),
         ALLSELECTED('Calendar')
      ),
      ISONORAFTER(
         'Calendar'[Adjusted Month No], MAX('Calendar'[Adjusted Month No]), DESC
      )
   )
)

EDIT: I've tried this as well, and it's getting me closer! The running total looks correct, except if a category is zero for a month, then that category completely disappears from that month's running total. 

kgCO2e (Cumulative) v2 =  CALCULATE( [kgCO2e (Cumulative)], FILTER( ALL( 'Calendar'[Date]), 'Calendar'[Date] <= MAX( 'Calendar'[Date] )))

[v-karpurapud]

Hi @alysonchu 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @alysonchu 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[Thejeswar]

Hi @alysonchu ,

You can simply use a DATESYTD for this

Regards,

[alysonchu]

Thanks @Thejeswar. That simplies the second thing I was trying to do, but I'm still having issues visualising this data when there is no data in a given month. (For example, if there were no sales in December, then there is no data for December, so a visualisation will show 0 even though there were cumulative sales).

[Thejeswar]

Hi @alysonchu ,

It is recommended to use the functions like DATESYTD() with tables having continuous dates.

However, you can try out the solution suggested by @v-karpurapud . But I think if your expenses doesn't have any entry for December, REMOVEFLTERS() won't help.

Alternatively, I would suggest that you introduce a new table that has continuous dates and MONTHS derived on those dates. This table should be related to your Expenses tables based on dates. That way you can get the Month Value from a table that has an entry for the Month and the Numbers can be got from Expenses.

Don't forget to enable "Show items with no data" on the Month Column in the visual

This should work unless there is something else that is preventing it

Regards,

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-karpurapud · Accepted solution]

Hi @alysonchu 

Thank you for reaching out to the Microsoft Fabric community forum.

The problem isn’t with your DAX logic, but rather with how the visual handles months that have no data. Since the Expenses table doesn’t include any rows for December, that month is automatically excluded from the axis. Without December in the filter context, DATESYTD doesn’t get that month as input, so the cumulative value looks like it resets.

To resolve this, make sure your visual displays all fiscal months (April–March), even if the fact table has no records for some months:

Place the Calendar table on the X-axis and enable X-axis → Show items with no data so months without transactions are still shown. Then, use a fiscal year running-total measure to correctly calculate cumulative values.

DAX

Cumulative Fiscal :=

CALCULATE(

SUM(Expenses[kgCO2e]),

DATESYTD('Calendar'[Date], "3/31"),

REMOVEFILTERS(Expenses)

)

With “Show items with no data” enabled, months like December will be included in the visual, and the cumulative total will carry forward correctly instead of dropping to zero.

If you have any further questions, feel free to reach out and we'll be glad to assist.
 

Regards,

Microsoft Fabric Community Support Team.

 

[rohit1991]

Hii @alysonchu 

Use a proper fiscal Year-To-Date calculation instead of adjusting month numbers manually. Since your year starts in April, use DATESYTD with a March 31 year-end. This correctly resets the cumulative total each April and avoids issues when future dates exist.

Cumulative Fiscal :=
CALCULATE(
    SUM(Expenses[Expense]),
    DATESYTD('Calendar'[Date], "3/31")
)

"3/31" tells Power BI that the fiscal year ends on March 31 (so it starts April 1). This ensures the running total restarts every April and remains accurate even when future dates are in the Calendar table.

[alysonchu]

Ah thank you! So that accomplishes the same thing as my wonky manual workaround, but I still have the issue that when there is no expense in a given month, it's not represented in the data. 

So for instance your provided Cumulative Fiscal calculation comes out like this if I filter by an expense type where there was no expense data in December:

Month	Cumulative Fiscal
October	12,594
November	14,238
January	20,102

But I'd like to have the data appear like this:

Month Cumulative Fiscal

Month	Cumulative Fiscal
October	12,594
November	14,238
December	14,238
January	20,102