Forum Discussion

tahechadv_2022's avatar
tahechadv_2022
Icon for Helper II rankHelper II
3 years ago

How to fill empty values from with values from previous month?

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
        )
)
data protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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/339586.
        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