Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi guys,
I need to fill the current month value with the previous month value without altering the total. Here's the example:
(Due to privacy of our finances data, it was required censor the data)
I need to take the value from "Julho" (July) and fill and "Agosto" (August) with these value WITHOUT altering the "Total" and it needs to be dynamic, in other words, the dax needs to take the values of the previous month, and IF the curent month is empty, it would replace it.
* the column with dates is called "data_lancamento"
The dax:
Acumulado Banco =
VAR Banco = VALUES('Capital de Giro'[banco])
VAR IDGeral = MAX('Capital de Giro'[ID Geral])
RETURN
CALCULATE(
SUM('Capital de Giro'[valor]),
FILTER(
ALL('Capital de Giro'),
'Capital de Giro'[ID Geral] <= IDGeral &&
'Capital de Giro'[banco] IN Banco
)
)
@tahechadv_2022 Can you provide a sample/example of your raw data?
Without comprimise data protection, not too much
@tahechadv_2022 Well, if you use Fill down in Power Query that is going to affect your total. You could do this in a measure although you will almost certainly encounter a measure total issue (maybe not). Basically in the measure, you could get the sum, VAR __Sum = SUM('Table'[condigo]). If it is BLANK, IF(__Sum = BLANK(), <previous row's value>, __Sum). You shoudl be able to get the previous row's value via some fancy filtering. See MTBF for an example.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
I understood the concept, but a doubt remained:
How would I apply the concept to my dax:
The new dax:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
My dax:
Acumulado Banco =
VAR Banco = VALUES('Capital de Giro'[banco])
VAR IDGeral = MAX('Capital de Giro'[ID Geral])
RETURN
CALCULATE(
SUM('Capital de Giro'[valor]),
FILTER(
ALL('Capital de Giro'),
'Capital de Giro'[ID Geral] <= IDGeral &&
'Capital de Giro'[banco] IN Banco
)
)
User | Count |
---|---|
73 | |
72 | |
39 | |
25 | |
23 |
User | Count |
---|---|
96 | |
93 | |
51 | |
43 | |
42 |