Cumulative result for previous Fiscal Year

[gunicotra]

Hi to everyone,

I have to calculate the cumulative running results for the current and previous fiscal year but I have some problems. I am using a slicer in order to choose the fiscal year (2021, 2020 etc.) on top of the report and I have calculated the measure for the current year as:

CumulatedFG = calculate(sum('TABLE'[Counter]),

filter(ALLSELECTED('TABLE'),

'TABLE'[DATA_IN]<=MAX('TABLE'[DATA_IN])

&&'TABLE'[YEAR]>=SELECTEDVALUE('TABLE'[YEAR])))

but it is working only if I use the on a matrix table or other visuals the "Year-Month" column (e.g.: 2021-01), otherwise some months may overlap the data from different fiscal years, or be wrongly ordered if I use only months.

For the previous year, considering I have selected 2021 as slicer, I tried this two solutions (both usuccessfuly!):

1° Solution:
CumulatedFG.PY = calculate(sum('TABLE'[Counter]),

filter(ALLSELECTED('TABLE'),

'TABLE'[DATA_IN]<=MAX('TABLE'[DATA_IN])

&&'TABLE'[YEAR]>=SELECTEDVALUE('TABLE'[YEAR])-1))

But with this I got the same results as for the current year!

2° Solution:

CumulatedFG.PY = calculate(sum('TABLE'[Counter]),

filter(ALLSELECTED('TABLE'),

'TABLE'[DATA_IN]<=MAX('TABLE'[DATA_IN]),

FILTER(ALL('TABLE'[YEAR]),'TABLE'[YEAR]>=VALUES('TABLE'[YEAR])-1))

And also with this solution I got the same results as for the current year!

May someone give some hints please?

Thanks you in advancce!

G

[v-xulin-mstf]

Hi @gunicotra,

Could you provide pbix file after removing sensitive information?

With the screenshots I am not sure what is causing this issue.

Best Regards,

Link

[gunicotra · Accepted solution]

Hi Link, thanks for you answer.
Since my last message, I tried several ways and at the end I think I found a solution using two different date tables. The problems were different, but the main were that:

1. the slicer was forcing the report to fileter only one year, while I needed more flexibility; 

2. the particular definition of a "Year" assumed that the cost elements were related in some cases to more than 12 months and in some other cases, to less than 12 months, and in this scenario also the visualisation with time-intelligent formulas was too strict.

For this reason I used a data date table for the current year (that selected bty the slicer) and another date table for the previous year, using a new calculated column to bring one year forward the data registered for the previous year. I know that maybe this is not the best solution, (I am using powerBI for less than one month!) but it works 🙂

G

[v-xulin-mstf]

Hi @gunicotra,

Congratulations! if your issue is solved, please adopt your solution to help others.

Best Regards,

Link

[gunicotra]

just to integrate a bit.... If I change the "Year-Month" framework with only "Months" I gest this result:

Summing months data between through different years and cumulating the results monthly results wrongly!