cumulate formula in power bi

[emanuela]

HI all,

i try to wokr on the cumulate formula but even if the formula seems to be correct the results make no sense. 

I have a dashboard with 2 filters fiels and 2 tables: the first just a regular sum and the second with the cumulate quantity per month using this formula "calculate(sum(CSV[OC QTY]),FILTER(ALL(csv),CSV[PIVOT DATE]<=max(CSV[PIVOT DATE])))".

As you can see the results make no sense.

can you help me on fix this?

thank yo so much

emanuela

[v-kongfanf-msft · Accepted solution]

Hi @emanuela ,

According to your description, the all function was used in your formula and it filtered out the filter criteria of the two external filters, resulting in incorrect results. I tried to modify your formula:

Cumulative Quantity = 
CALCULATE(
    SUM(CSV[OC QTY]),
    FILTER(
        ALL(CSV),
        CSV[PIVOT DATE] <= MAX(CSV[PIVOT DATE]) && 
        YEAR(CSV[PIVOT DATE]) = SELECTEDVALUE(DateTable[Year]) && 
        MONTH(CSV[PIVOT DATE]) = SELECTEDVALUE(DateTable[Month])
    )
)

If the problem persists, please provide relevant test data and screenshot information.

Best Regards,
Adamk Kong

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

[emanuela]

Hi Adam,

thank you but still is not working. it shows me this error "A single value for variaton 'Year' for column 'Date' in table 'calendario' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."...

how can i fix it?

best,

emanuela

[Uzi2019]

Hi @emanuela 
You can try this formula. I hope this gives you correct answer

12 month Rollback = CALCULATE(SUM(Table[Clicks]),DATESINPERIOD('Calendar'[Date],Max('Calendar'[Date]),-12,MONTH))

I hope I answered your question!

[emanuela]

hi thank you but it's not working, here waht i got (first just regular sum, second table usign your formula):

thanks for clarify