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'm facing an issue in the last 3 days. I thought I can solve it, but no luck.
I have a measure with LY Amount:
LY Amount = CALCULATE([CY Amount],SAMEPERIODLASTYEAR(vw_Prem_ToO_ID[Date]),ALL(vw_Prem_ToO_ID[Date]))
Cumulative CY Amount =
IF(
MIN(vw_Prem_ToO_ID[Date]) <= CALCULATE(MAX(vw_Prem_ToO_ID[Date]),ALL(vw_Prem_ToO_ID)),
CALCULATE(([CY Amount]),FILTER(ALLSELECTED(vw_Prem_ToO_ID),vw_Prem_ToO_ID[Date] <= MAX(vw_Prem_ToO_ID[Date])),VALUES(TradeTable[TradeCategory])))
(The TradeCategory just added to split by category in a linechart later)
This is also working fine with the "CY Amount", but not working with the PY Amount.
I have tried almost everything.
I have checked many code, tried in different ways, but The results I got: empty, LY Amount (not the cumulative), Sum of the LY Amount.
I would like to have it in Month split like the CY.
May it's messy, but If you know anything would be helpful, I would appreciate.
Cumulative LY Amount=
var MinDate = MIN(vw_Prem_ToO_ID[Date])
var MaxDate = MAX(vw_Prem_ToO_ID[Date])
var PYearBottom = DATE(YEAR(MinDate)-1,MONTH(MinDate),DAY(MinDate))
var PYearTop = DATE(YEAR(MaxDate)-1,MONTH(MaxDate),DAY(MaxDate))
return
/*IF(
PYearBottom <= CALCULATE(MaxDate,ALL(vw_Prem_ToO_ID)),
CALCULATE(([CY Amount]),FILTER(ALLSELECTED(vw_Prem_ToO_ID),PYearBottom <= PYearTop),VALUES(Trade[TradeCategory])))*/
---IF(PYearBottom <= CALCULATE(PYearTop,ALL(vw_Prem_ToO_ID)),
--- CALCULATE([CY GWP],FILTER(ALLSELECTED(vw_Prem_ToO_ID),vw_Prem_ToO_ID[Date] <= PYearTop)
---))
---CALCULATE([CY Amount],SAMEPERIODLASTYEAR(vw_Prem_ToO_ID[Date]))
CALCULATE([LY Amount],FILTER(ALLSELECTED(vw_Prem_ToO_ID),vw_Prem_ToO_ID[Date] <= MAX(vw_Prem_ToO_ID[Date])))
This are and the combination of these I have tried.
Thanks, Denes
Solved! Go to Solution.
@dexter2424 , Not very clear. Have to try formula like?
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
@dexter2424 , Not very clear. Have to try formula like?
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
No, I have missed this, but helped me a lot!
Thank You!
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |