Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

FillDown function based on a measure

Hey! 

 

I am creating a dashboard that gives an overview of the current states of transaction accounts and it has to show every day (not just the current day). To create this we get information from a SQL server and use a measure to create the cumulative SUM of the transactions (see below):

 
CumulatieveSom = CALCULATE ( SUM (CompleteList[Remaining Amount]),
FILTER ( ALL (CompleteList[Posting Date]),
CompleteList[Posting Date] <= MAX (CompleteList[Posting Date])))
 
This formula only shows the new values on dates transactions took place. I would like to fill the blank cells with the previous end amount. As I know this is possible with the Fill Down function when talking about solid data, I was wondering if this is also possible when it should be performed over a measure.
 
The visual is a matrix with the Posting Dates on the rows and the account numbers on the columns... The values are based on the cumulative SUM DAX.
Need Help

6 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    Hi Bianca,

     

    Can you show what the current visual looks like? It would help in understanding what you are trying to achieve. 

    By the understanding that I have now, you want the total sum of every day in a matrix, or do you want every day added to the next day and show that in a matrix? 

    Are you using a date table by any chance?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This is the visual without the numbers (as I am not allowed to share them)... In the current situation, only cells that have transactions on that date are filled. For example, Account 1310 has transactions on 11/11 and on 18/11, those cells are filled (11/11 with an end amount and 18/11 with the end amount of 11/11 + transactions to form a new end amount). The cells in between are empty and should be filled with the data from the cell 11/11 without it interfering with the calculation of 18/11.

       

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can use a spreate date table as the row field to meet your requirement, please also refer to this similar thread: https://community.powerbi.com/t5/Desktop/Replacing-blank-cells-in-Matrix-visuals/td-p/612814

     

     

    t = DISTINCT('CompleteList'[Posting Date])

     

     

     

    CumulatieveSom = 
CALCULATE (
    SUM ( CompleteList[Remaining Amount] ),
    FILTER (
        ALL ( CompleteList[Posting Date] ),
        CompleteList[Posting Date] <= MAX ( t[Posting Date] )
    )
)

     

     


    Best regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-lid-msft This does not work as it messes with the cumulative function (I now see the final value of today in every cell and the cells with no transactions are still empty)

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Have you tried to use the date column in the new calculated table as the row fied instead of using the one in origin table?


        Best regards,