Forum Discussion

CasperSV's avatar
CasperSV
Icon for Helper II rankHelper 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
v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, CasperSV 

 

I really don't understand what you mean. . . Can you say more complete?

Can you share a sample file and your desired result like? So we can help you soon.

 

Best Regards,
Community Support Team _ Janey

 

CasperSV's avatar
CasperSV
Icon for Helper II rankHelper II
4 years ago

Hi v-janeyg-msft 

 

I'm sorry, to make it more clear; this is my desired result

So, GL amount should show the amount of the month selected.

GL amount till last period should show the cumulative amount before the month selected. This is a measure.

 

I hope this will make it more clear.

 

Casper