Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CasperSV
Helper II
Helper II

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: 

CasperSV_2-1641225637216.png

 

CasperSV_0-1641225177148.png

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

2 ACCEPTED SOLUTIONS
Shahfaisal
Solution Sage
Solution Sage

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] ) )
    )
)

 

 

View solution in original post

@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

View solution in original post

14 REPLIES 14
v-janeyg-msft
Community Support
Community Support

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
v-janeyg-msft
Community Support
Community Support

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.

vjaneygmsft_0-1641449877593.png

Best Regards,
Community Support Team _ Janey

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

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

 

Hi @v-janeyg-msft 

 

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

CasperSV_0-1643282106064.png

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

Hi @v-janeyg-msft 

 

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

CasperSV_0-1643282106064.png

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

@v-janeyg-msft 

 

Sorry forgot the 12 (december)

 

CasperSV_1-1643282703410.png

 

CasperSV
Helper II
Helper II

Hi Shahfaisal,

 

Thanks for the reply.

I am trying the calculation, however something is wrong.

CasperSV_0-1641288725748.png

 

Do you have any idea?

Month = Maand

Just use your date key. If posting date is your date key then it needs to be Tabel[Posting date].

Unfortunately it is not working.

With this formula :

CasperSV_1-1641372610185.png

 

The colums are zero, also when I am filtering on october or november:

 

CasperSV_0-1641372547104.png

 

I have tried <= MAX ((Tabel[posting date])) as well and results in the following:

CasperSV_2-1641373120401.png

When filtering on november:

CasperSV_3-1641373198730.png

 

However, GL amount till last period should be zero for Packaging materials.

And work clothes should be GL amount till last period 200.

 

 

 

 

 

 

 

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.

Shahfaisal
Solution Sage
Solution Sage

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 

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 

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors