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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.