Amount Total - Filter View

[Anonymous]

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. 

[v-yuta-msft]

Hi bfra4,

Could you clarify more details about your requirement and share some sample data?

Regards,

Jimmy Tao

[Anonymous]

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. 

[Greg_Deckler]

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

[Anonymous]

Thank you very much @Greg_Deckler,  wasn't aware of this subject !