Forum Discussion

CasperSV's avatar
CasperSV
Helper II
4 years ago
Solved

Dynamic table based on filter

Hi everyone,   I am trying to make a Profit and Loss statement in which I want to see what the total amount is till a filtered period.   A quick and simple example:    If november i...
Need Help
  • Shahfaisal's avatar
    Shahfaisal
    4 years ago

    I think you are looking for a 'life to date' calculation.  Try somehting like below. You will need a date dimension to make this work.

     

     

    CALCULATE (
    SUM(GLTable[GLAmount]),
    FILTER (
        ALL ( Date[Datekey] ),
        Date[Datekey] < MAX ( ( Date[Datekey] ) )
    )
)

     

     

  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    CasperSV 

     

    Add a year filter.

    Like:

    measure =
CALCULATE (
    SUM ( GLTable[GLAmount] ),
    FILTER (
        ALL ( Date[Datekey] ),
        Date[Datekey] < MAX ( ( Date[Datekey] ) )
            && YEAR ( Date[Datekey] ) = SELECTEDVALUE ( Date[Datekey] )
    )
)

     

    Best Regards,
    Community Support Team _ Janey
CasperSV's avatar
CasperSV
Helper II
4 years ago

Hi Shahfaisal,

 

Thanks for the reply.

I am trying the calculation, however something is wrong.

 

Do you have any idea?

Month = Maand

  • Shahfaisal's avatar
    Shahfaisal
    Solution Sage
    4 years ago

    Just use your date key. If posting date is your date key then it needs to be Tabel[Posting date].

    • CasperSV's avatar
      CasperSV
      Helper II
      4 years ago

      Unfortunately it is not working.

      With this formula :

       

      The colums are zero, also when I am filtering on october or november:

       

       

      I have tried <= MAX ((Tabel[posting date])) as well and results in the following:

      When filtering on november:

       

      However, GL amount till last period should be zero for Packaging materials.

      And work clothes should be GL amount till last period 200.

       

       

       

       

       

       

       

      • Shahfaisal's avatar
        Shahfaisal
        Solution Sage
        4 years ago

        As I mentioned in my first post, you will need a date dimension for the code to work. From your code, it looks like you are using date from the GL table. Create a date dimension, link the GL table with the date dimension (table) and then modify the DAX code to look like the code I pasted in my first post.