cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable
 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)
15 REPLIES 15
Anonymous
Not applicable
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
Helper I

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
Not applicable

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

Helper I

Can it be solved?

Anonymous
Not applicable

Does you Date format dis a date Hour?

Helper I

No, only "date"

Anonymous
Not applicable

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

Helper I

Nothin, but I can create a index column.

Anonymous
Not applicable

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

keep me in touch

Helper I

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

Anonymous
Not applicable
 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)
Helper I

Thank-you SO MUCH @Anonymous !!!

Anonymous
Not applicable

Welcome

Enjoy Dax 🙂

Anonymous
Not applicable

i

Try this

Super User

@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]) )