How to calculate the difference of a column using the first the of month and last day of month value

[Tahech]

Hi guys,

I'm trying to calculate the difference of the value first day and the last the of the month.

Example:

day              -  value

01/01/2022 - 1

05/01/2022 - 2

20/01/2022 - 5

29/01/2022 - 5

03/02/2022 - 5

13/02/2022 - 3

17/02/2022 - 3

28/02/2022 - 5

The first month is January, it started with 1 in 01/01/2022 and ended with 5 in 28/01/2022, the difference between them is 4.
And the following month is 0, because it started with 5 and ended with 5.

How can I calculate it for ALL months in my database?

[Anonymous]

diff =

var mindate=calculate(MIN('Ecart date'[Date]))

var maxdate=calculate(max('Ecart date'[Date]))

var compteurmini=calculate(max('Ecart date'[Compteur]),'Ecart date'[Date]=mindate)

var compteurmaxi=calculate(max('Ecart date'[Compteur]),'Ecart date'[Date]=maxdate)

var result=compteurmaxi-compteurmini

return

result

Table='Ecart date'

Compteur==> Uour last number on your exemple

[Tahech]

It doesn't work

diff =

var mindate=calculate(MIN('# Pagas Recebidas'[Data]))

var maxdate=calculate(max('# Pagas Recebidas'[Data]))

var compteurmini=calculate(max('# Pagas Recebidas'[Saldo]),'# Pagas Recebidas'[Data]=mindate)

var compteurmaxi=calculate(max('# Pagas Recebidas'[Saldo]),'# Pagas Recebidas'[Data]=maxdate)

var result=compteurmaxi-compteurmini

return

result

I think it does not work because there are more than 1 value in each date.

(It is in portuguese, Monday, 25 April of 2022)

It's probably summing it all, I need the last of the last value.

[Anonymous]

Yes for sure it is for this. Do you have the time with the date because it taking the minuimum date and if you have more than 1 record at the same date it will not work

[Tahech]

Can it be solved?

[Anonymous]

Does you Date format dis a date Hour?

[Tahech]

No, only "date"

[Anonymous]

Esxcept the date do you have an index or another column you could use to identify the first record from a same date ?

[Tahech]

Nothin, but I can create a index column.

[Anonymous]

Yes could be usefull because Pwbi cannot define the mninimum value.

keep me in touch

[Tahech]

I created a column called "index", starting in 0.

[Anonymous · Accepted solution]

To add as variable

var mindateindex=calculate(MIN(Index),('# Pagas Recebidas'[Data])=Mindate)

var maxdateindex=calculate(Index),max('# Pagas Recebidas'[Data])Maxdate)

change thses 2 lines

var compteurmini=calculate(max('# Pagas Recebidas'[Saldo]),'# Pagas Recebidas'[Data]=mindate&&Index=mindateindex)

var compteurmaxi=calculate(max('# Pagas Recebidas'[Saldo]),'# Pagas Recebidas'[Data]=maxdate&&index=maxdateindex)

[Tahech]

Thank-you SO MUCH @Anonymous !!!

[Anonymous]

Welcome

Enjoy Dax 🙂

[Anonymous]

i

Try this

[amitchandak]

@Tahech , You can use firstnonblankvalue and lastnonblankvalue

calculate(firstnonblankvalue ('DAte'[DAte], sum(Table[Value]))

or

calculate(firstnonblankvalue ('DAte'[DAte], sum(Table[Value]), allexcept(Table, 'Date'[Month Year]) )

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak