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.
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
@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?
@Krau5i , The field will be fiscal year month Num. Make sure it numerical incremental column (even text should be incremental number format) LIKE YYYYMM
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?
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?
@Krau5i Have you tried the standard Running Total quick measure?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |