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
Shahfaisal's avatar
Shahfaisal
Solution Sage
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] ) )
    )
)

 

 

CasperSV's avatar
CasperSV
Helper II
4 years ago

v-janeyg-msft 

Sorry for the inconvenience, I have tried this calculation again with a new independent Calendar and it worked :).

One last thing, it should reset when entering a new fiscal year. When I select january 2022, it calculates the sum of 2021. Any idea?

 

Best regards,

Casper 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    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