Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tahech
Helper I
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)

View solution in original post

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

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)
Tahech_0-1653492536763.png

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

Can it be solved?

Anonymous
Not applicable

Does you Date format dis a date Hour?

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 ?

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

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)

Thank-you SO MUCH @Anonymous !!!

Anonymous
Not applicable

Welcome

 

Enjoy Dax 🙂

Anonymous
Not applicable

i


Try this

amitchandak
Super User
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]) )

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.