topic Re: Cumulative ratio measure for two years from the month of 2019 to the month of 2022. in DAX Commands and Tips

Cumulative ratio measure for two years from the month of 2019 to the month of 2022.

jesusssss — Tue, 18 Oct 2022 12:10:42 GMT



In this table we have the dates from 12-01-2019 to the current date and what we want to do is 
an accumulated ratio of this table with which we try to get these measurements.

A measure that indicates the total guarantees claimed in two years, that is, 
the guarantees from January 2020-January 2022 from February 2020-February 
2022 and so that the accumulated is done automatically with all the months.
 

I await your response because I'm already desperate I don't know what else to try.

Thank you very much.

Re: Cumulative ratio measure for two years from the month of 2019 to the month of 2022.

amitchandak — Tue, 18 Oct 2022 17:56:42 GMT

jesusssss , With help from a date table

Cumulative % = divide(CALCULATE(SUM(Table[Grt]),filter(all('Date'),'Date'[date] <=max('Date'[date]))), CALCULATE(SUM(Table[Grt]), all('Date') ) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Cumulative ratio measure for two years from the month of 2019 to the month of 2022.

jesusssss — Wed, 19 Oct 2022 10:40:53 GMT

First of all thank you very much for trying to solve our problem, we have tried
 to use that command that you have put but it does not work.

I enclose the measurement that you have given me but it does not work for me 
even so I think that perhaps I have been able to put some wrong data.

These images that I attach correspond to the formula that we want to carry 
out in Excel and the tables that are seen more clearly in the power bi.


This is the formula that we have in Excel to calculate the indicator of total guarantees 
for 2 years in case it helps you. =IF(D27>0;C43+D27-'2020'!D11;"")


This is the calendar where we get the dates.

This is the chart we want what happens is that in this case the data is wrong 
because the indicator should go above the red trend line

I enclose a pdf with my answer so that it can be seen more clearly than here

Thank you very much again and I look forward to your response.

Re: Cumulative ratio measure for two years from the month of 2019 to the month of 2022.

amitchandak — Wed, 19 Oct 2022 10:42:54 GMT

jesusssss ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Cumulative ratio measure for two years from the month of 2019 to the month of 2022.

jesusssss — Wed, 19 Oct 2022 11:47:28 GMT

Here I give you the tables with all the data in the 2020 capture where the graph is there I have put the excel formula and all the data we use is in yellow

Re: Cumulative ratio measure for two years from the month of 2019 to the month of 2022.

jesusssss — Thu, 20 Oct 2022 06:19:45 GMT

amitchandak Do you know how we could do the measure?

Re: Cumulative ratio measure for two years from the month of 2019 to the month of 2022.

jesusssss — Fri, 21 Oct 2022 12:15:44 GMT

In the end you were right, it was the formula that you gave me nothing more than removing the last part

the formn: cumulative %= (calculate(sum(garantias[grt],filter(all('calendario'[fecha])))

Thank you very much and sorry for the inconvenience you are the best

amitchandak