Forum Discussion
Calculate cumulative
Hello, how are you?
I have a question,
I am trying to calculate this:
Every year since this year, I start with €100,000 to distribute.
And every month I'm spending, so I have less left.
And in 2023 it would restart again at 100.000€.
I suppose that there is some way to calculate it, but in PBI I have not been able to do it, and the previous time I ended up programming it in SQL.
I need it to be like this
But I have this instead
Can anybody help me??
Thanks
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below.
All measures are in the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your dataset.
3 Replies
afrutos , BAsed on what I got. Refer
Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400
Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below.
All measures are in the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your dataset.
Hi thanks a lot,
I finally investigated and find the solution but doing in a different way, maybe it's wrong but it seems to wrok. I share it with you all just in case it could help to others.
YTD measure = SWITCH(TRUE(),ISBLANK(SUM(PROYECTA[IMPORTE])), BLANK(),TOTALYTD(SUM(PROYECTA[IMPORTE]),Tabla[Date]))REMAINING MEASURE = SWITCH(TRUE(),ISBLANK(MEDIDAS[YTD MEASURE]), BLANK(),100000 - MEDIDAS[YTD MEASURE])
But I proved your approach Jihwan_Kim and it's working as well, so thanks a lot.
Now I don't have date from other years except 2022, so I hope it works on 2023. At the end I used you solution. 😉
