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

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.