March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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?
Solved! Go to 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) |
It doesn't work
It's probably summing it all, I need the last of the last value.
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?
Does you Date format dis a date Hour?
No, only "date"
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.
Yes could be usefull because Pwbi cannot define the mninimum value.
keep me in touch
I created a column called "index", starting in 0.
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 !!!
Welcome
Enjoy Dax 🙂
i
Try this
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
92 | |
91 | |
79 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
72 | |
55 |