Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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] ) )
)
)
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] )
)
)
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.
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.
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.
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
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
Hi Shahfaisal,
Thanks for the reply.
I am trying the calculation, however something is wrong.
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 :
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.
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.
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] ) )
)
)
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
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] )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.