Running total with year-month-number problem

[Krau5i]

Hi all,

I feel a little silly about posting this question as it should be very simple to solve and there are numerous posts on this topic already. However, I was not able to solve the issue despite weeks of research on this forum and other blog posts.

I want to make a running total that accumulates all values of a single field accross fiscal years. As recommended, I've tried to use the CALCULATE() formula, however it doesn't give the expected result. It shows a single random amount for all periods and does not aggreate any date. Below the formula:  

I've tried multiple other formulas, but never got the cumulation. I was thinking maybe I should bring in the date, however, this date is on a differen calendar table, and I was not sure if I can refer to this table in the calculate formula.

I have 2 data tables that might be relevant for this issue. My data table (ProjectLifeCylcleTotal) only has FiscalYearMonthNum (format: 202001, 202002, 202003, etc.) available. This is related to my calendar table as shown below.

Does anyone have an idea how to solve this issue?

Thanks in advance,

Tobias

[Krau5i]

@Greg_Deckler Thanks for the advise. I wasn't aware about the quick measure. When I select it, it asks for a "Field". I'm assuming this could be a year, or quarter, etc. but I just want it aggregated over years and quarters. Do you have any advise what to use in this field?

[amitchandak]

@Krau5i , The field will be fiscal year month Num.  Make sure it numerical incremental column (even text should be incremental number format) LIKE YYYYMM

[Krau5i]

Hi @amitchandak!

Thanks! I realised, that is aggreating only at the defined field fiscal year month num. Does that mean I cannot apply other custom hierarchies from my calendar table for this measure? I will have to create another measure for e.g. fiscal quarter num, fiscal year, etc? I forsee this being complicated when working with charts or tables that have any sort of drill down functionalities. Do you have any advice how to handle this?

[Krau5i]

Does anyone have any input on this question? My issue is that the cummulation is only working on the defined "FiscalYearMonthNum" field. I would not like to use it in any charts, and rather choose a different field from my calendar table. Is that possible?

[Greg_Deckler]

@Krau5i Have you tried the standard Running Total quick measure?