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
I have been trying solutions but i just don't get it
I want to do the following on Power BI
Like a sum with condition on date and on the the same product x lob
I tried TotalYTD but it didn't work.
Does someone knows how to do it ?
Solved! Go to Solution.
Hi, @hidrag
According to your description, I can roughly understand your requirement, you want to sum the "amount" if it's the same product and Lob for all valuation date anterior to the one in the line and create a slicer to filter the data based on the date column, right? I think you can create a measure to use the AllSelected() function instead of All() like this to achieve your requirement:
Cumulative Amount 1 =
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[LoB]=MAX('Table'[LoB])&&[Valuation Date]<=MAX('Table'[Valuation Date])))
And you can get what you want, like this:
You can download my test pbix file below
For more info about the Allselected() function in DAX, please refer to this link:
https://docs.microsoft.com/en-us/dax/allselected-function-dax
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @hidrag
According to your description, I can roughly understand your requirement, you want to sum the "amount" if it's the same product and Lob for all valuation date anterior to the one in the line and create a slicer to filter the data based on the date column, right? I think you can create a measure to use the AllSelected() function instead of All() like this to achieve your requirement:
Cumulative Amount 1 =
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[LoB]=MAX('Table'[LoB])&&[Valuation Date]<=MAX('Table'[Valuation Date])))
And you can get what you want, like this:
You can download my test pbix file below
For more info about the Allselected() function in DAX, please refer to this link:
https://docs.microsoft.com/en-us/dax/allselected-function-dax
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@hidrag you can use the following measure
Measure =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Valuation Date] <= MAX ( 'Table'[Valuation Date] )
),
KEEPFILTERS ( SUMMARIZE ( 'Table', 'Table'[Product], 'Table'[LoB] ) )
)
If you need calculated column
cumulativeSum =
SUMX (
FILTER (
'Table',
'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[LoB] = EARLIER ( 'Table'[LoB] )
&& 'Table'[Valuation Date] <= EARLIER ( 'Table'[Valuation Date] )
),
'Table'[Amount]
)
Thanks I'll have a look.
The data is below. The output is col "Cumulative Amount 1"
It sums "amount" if it's the same product and Lob for all valuation date anterior to the one in the line
Product | LoB | Valuation Date | Amount | Amount 2 | Month | Cumulative Amount 1 |
A | Accident | 01/01/2021 | 10 | 100 | January | 10 |
A | Accident | 01/02/2021 | 20 | 200 | February | 30 |
A | Accident | 01/03/2021 | 30 | 300 | March | 60 |
A | Accident | 01/04/2021 | 40 | 400 | April | 100 |
A | Accident | 01/05/2021 | 50 | 500 | May | 150 |
A | Accident | 01/06/2021 | 60 | 600 | June | 210 |
A | Accident | 01/07/2021 | 70 | 700 | July | 280 |
A | Accident | 01/08/2021 | 80 | 800 | August | 360 |
A | Accident | 01/09/2021 | 90 | 900 | September | 450 |
A | Accident | 01/10/2021 | 100 | 1000 | October | 550 |
A | Motor | 01/01/2021 | 150 | 250 | January | 150 |
A | Motor | 01/02/2021 | 200 | 300 | February | 350 |
A | Motor | 01/03/2021 | 250 | 350 | March | 600 |
A | Motor | 01/04/2021 | 300 | 400 | April | 900 |
A | Motor | 01/05/2021 | 350 | 450 | May | 1250 |
A | Motor | 01/06/2021 | 400 | 500 | June | 1650 |
A | Motor | 01/07/2021 | 450 | 550 | July | 2100 |
A | Motor | 01/08/2021 | 500 | 600 | August | 2600 |
A | Motor | 01/09/2021 | 550 | 650 | September | 3150 |
A | Motor | 01/10/2021 | 600 | 700 | October | 3750 |
@hidrag See if this helps: S Excel to DAX Translation - Microsoft Power BI Community
Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
OK so i found this
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.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |