Forum Discussion
calculate Delta dynamic different dates
Hello! I have a problem with a delta calculation and I have not been able to give a solution, I hope someone can help me and thanks in advance! What do I require: I require a calculation for the v...
- Due to fact that your sample data contains a rows only for 2 months (Oct and Nov) I will show you how to do that on dates and you can simply do that same for months 🙂Helper measuers:This measure is not needed for that case, but maybe you will need that later to check if you you want difference for dates or months (depending what is filtered).max date = lastdate(SampleData[DTM_ANIO_MES_DIA])second max date =
var max_date = [max date]
return CALCULATE([max date],FILTER(ALLSELECTED(SampleData),SampleData[DTM_ANIO_MES_DIA]<max_date))Saldo = SUM(SampleData[saldo diario])​Measures:
Saldo for max date = var ref_date = [max date] return CALCULATE([Saldo],SampleData[DTM_ANIO_MES_DIA]=ref_date)Saldo for second max date = var ref_date = [second max date] return CALCULATE([Saldo],SampleData[DTM_ANIO_MES_DIA]=ref_date)Difference = [Saldo for max date] - [Saldo for second max date]Example #1:
Non of dates selected:
Example #2:
Two dates are selected.
Example #3:
Three dates are selected (note that it only shows value for 2 latest one)
Example #4:
One date selected (nothing to compare to). What to do?
Due to fact that your sample data contains a rows only for 2 months (Oct and Nov) I will show you how to do that on dates and you can simply do that same for months 🙂
Helper measuers:
This measure is not needed for that case, but maybe you will need that later to check if you you want difference for dates or months (depending what is filtered).
max date = lastdate(SampleData[DTM_ANIO_MES_DIA])
second max date =
var max_date = [max date]
return CALCULATE([max date],FILTER(ALLSELECTED(SampleData),SampleData[DTM_ANIO_MES_DIA]<max_date))
var max_date = [max date]
return CALCULATE([max date],FILTER(ALLSELECTED(SampleData),SampleData[DTM_ANIO_MES_DIA]<max_date))
Saldo = SUM(SampleData[saldo diario])​
Measures:
Saldo for max date =
var ref_date = [max date]
return CALCULATE([Saldo],SampleData[DTM_ANIO_MES_DIA]=ref_date)Saldo for second max date =
var ref_date = [second max date]
return CALCULATE([Saldo],SampleData[DTM_ANIO_MES_DIA]=ref_date)Difference = [Saldo for max date] - [Saldo for second max date]
Example #1:
Non of dates selected:
Example #2:
Two dates are selected.
Example #3:
Three dates are selected (note that it only shows value for 2 latest one)
Example #4:
One date selected (nothing to compare to). What to do?
Thank you!
It works perfectly, I replicated the measurements to calculate the months and it worked perfectly!