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 is selected/filtered, I want to see the total amount of the G/L till october 2021. In this case, the €500 Packaging materials should be on the Total mutations selected period. 

If october is selected/filtered, I want to see the total amount of the G/L till september 2021. In this case, the €200 work clothes should be on the Total mutations selected period, and Packaging materials is zero because nothing is posted in october or before.

 

 

 

 

Do you guys have any idea how to fix this?

 

Thank you in advance,

Casper

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

14 Replies

  • 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
  • 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.

         

         

         

         

         

         

         

  • 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

         

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

    Hi, CasperSV 

     

    I see that you haven't replied, has your problem been solved?

    If you still need help, please feel free to ask me. Welcome to share your solution, you can mark it as solution to help more people.

     

    Best Regards,
    Community Support Team _ Janey