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

Hi, CasperSV 

 

Your needs are not difficult, but I don’t know what display effect you want?

Is my demo correct? If it’s wrong, please point it out.

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

Hi v-janeyg-msft 

 

You are almost correct. When selecting 10 (October), 'Total mutations till last period' should be 0 in the 'Packaging materials' row. Because it was posted in 11 (November) and 'Total mutations...' should be the mutations from January till October.

 

Best regards,

Casper

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

    • CasperSV's avatar
      CasperSV
      Helper 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