Forum Discussion
Amount Total - Filter View
Hi,
I need some help. I have this measure:
Amount Total:=
Var vVersion = IF(HASONEVALUE('Version'[Version Code]), VALUES('Version'[Version Code]),"001")
Return
Int(CALCULATE(Sum('Fact PnL'[Amount]), 'Version'[Version Code]=vVersion))
And I have these measure: Amount FY, Amount YTD, Amount MTD and Amount YTG. And i've a View Table with view column (YTD, MTD, FY, YTG)
I want to filter in the metric of the "amount total", the metrics of the remaining amount, for example, when I click on the FY view, get the metric of the amount FY.
Best regards.
4 Replies
Not making heads or tails of this. 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
Thank you very much Greg_Deckler, wasn't aware of this subject !
Hi bfra4,
Could you clarify more details about your requirement and share some sample data?
Regards,
Jimmy Tao
Hi v-yuta-msft, of course :)
I've this scenario:
I've these meausres:Amount FY:=
Var FirstDayOfYear = DATE(MIN('Calendar'[Year]),01,01)
Var LastDayOfYear=DATE(MAX('Calendar'[Year]),12,31)
Var FilterDates=DATESBETWEEN('Calendar'[Date],FirstDayOfYear,LastDayOfYear)
Var Result = CALCULATE([Amount Total], FilterDates,filter('Version','Version'[Version Code]="BA"))RETURN
Result
Amount MTD:=TOTALMTD([Amount Total],'Calendar'[Date])
Amount YTD:=TOTALYTD([Amount Total], 'Calendar'[Date])
Amount YTG:= CALCULATE([Amount FY]-[Amount YTD])
Amount Total:=
Var vVersion = IF(HASONEVALUE('Version'[Version Code]), VALUES('Version'[Version Code]),"001")
Return
Int(CALCULATE(Sum('Fact PnL'[Amount]), 'Version'[Version Code]=vVersion))
STU:=
IF(
HASONEVALUE('Time Dimension'[View]),
VAR vVersion = IF(HASONEVALUE('Versions Study'[GLDataTypeStudy]), VALUES('Versions Study'[GLDataTypeStudy]),"001")
VAR vTimeKeyStudy = IF(HASONEVALUE('Versions Study'[TimeKeyStudy]), VALUES('Versions Study'[TimeKeyStudy]),"TY")
VAR PeriodName = VALUES('Time Dimension'[View])
VAR LastDaySelected = IF(vTimeKeyStudy="TY",MAX ( 'Calendar'[Date] ), MAXX(DATEADD('Calendar'[Date],-1,YEAR),'Calendar'[Date]))
VAR LastMonthSelected =
MONTH ( LastDaySelected )
VAR LastQuarterMonthSelected =
FLOOR ( LastMonthSelected - 1, 3 ) + 1
VAR LastYearSelected =
YEAR ( LastDaySelected )
Var LastDayOfYear=DATE(LastYearSelected,12,31)
VAR FirstDaySelected =
DATE (
LastYearSelected,
SWITCH (
PeriodName,
"MTD", LastMonthSelected,
"YTD", 1,
"YTG", MONTH(LastDaySelected+1)
),
1
)
VAR FilterDates =
DATESBETWEEN (
'Calendar'[Date],
FirstDaySelected,
IF(PeriodName="YTG",LastDayOfYear,LastDaySelected )
)
VAR Result = int(CALCULATE([Amount Total], FilterDates, 'Version'[Version Code]=vVersion))
RETURN
Result
)
REF:=
IF(
HASONEVALUE('Time Dimension'[View]),
VAR vVersion = IF(HASONEVALUE('Reference Study'[GLDataTypeStudy]), VALUES('Reference Study'[GLDataTypeStudy]),"BA")
VAR vTimeKeyStudy = IF(HASONEVALUE('Reference Study'[TimeKeyStudy]), VALUES('Reference Study'[TimeKeyStudy]),"TY")
VAR PeriodName = VALUES('Time Dimension'[View])
VAR LastDaySelected = IF(vTimeKeyStudy="TY",MAX ( 'Calendar'[Date] ), MAXX(DATEADD('Calendar'[Date],-1,YEAR),'Calendar'[Date]))
VAR LastMonthSelected =
MONTH ( LastDaySelected )
VAR LastQuarterMonthSelected =
FLOOR ( LastMonthSelected - 1, 3 ) + 1
VAR LastYearSelected =
YEAR ( LastDaySelected )
Var LastDayOfYear=DATE(LastYearSelected,12,31)
VAR FirstDaySelected =
DATE (
LastYearSelected,
SWITCH (
PeriodName,
"MTD", LastMonthSelected,
"YTD", 1,
"YTG", MONTH(LastDaySelected+1)
),
1
)
VAR FilterDates =
DATESBETWEEN (
'Calendar'[Date],
FirstDaySelected,
IF(PeriodName="YTG",LastDayOfYear,LastDaySelected )
)
VAR Result = int(CALCULATE([Amount Total], FilterDates, 'Version'[Version Code]=vVersion))
RETURN
Result
)
When i filtering by YTD and MTD the metric values of STU and REF are correct. When filter by YTG and FY are not correct :\If you need more details, please tell me !
Thanks for you attention,
Best regards.
