Forum Discussion

jzhao's avatar
jzhao
Frequent Visitor
2 years ago

Hide Selection but actually use data

Hello everyone, I need help creating a visual that compares the sales of Fiscal Year (FY) and Fiscal Year-1 (FY-1). Currently, I am using a slicer, and the idea is that when I select FY23, the visua...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
2 years ago

could you post some data and let us see how it shall work. 

jzhao's avatar
jzhao
Frequent Visitor
2 years ago

Source Data Power Query Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdlLilxJEETRvWgs6AiP/1qE9r+NRhQqUNqZ+iiNPMTgvl+/fvT/+n/Vqv/4+aO39uP3zz+nytPI08zTytPO08nTzdPLU2+44fd3DOhY0DGhY0PHiI4VHTM6dhR2lP4H7CjsKOwo7CjsKOwo7CjsGNgxYkclsUpilcQqiVUSqyRWSaySWCWxArECsQKxArECsQKxArECsQKxArECsQKxArECsQKxArECsQKxArGRTEYyGclkJJORTEYyGclkJJORTAaYDDAZYDLAZIDJAJMBJgNMBpgMMBlgMsBkgMkAkwEmA0wGmAwwGWSSL9HASzTwEs0kNpPYTGIzic0kNpPYTGIzic0kNkFsgtgEsQliE8QmiE0QmyA2QWyC2ASxCWITxCaITRCbIDZBbILYBLEJYhPEVnJayWklp5WcVnJayWklp5WcVnJa4LTAaYHTAqcFTgucFjgtcFrgtMBpgdMCpwVOC5wWOC1wWuC0wGmB0wKnRU75Yu0ktpPYTmI7ie0ktpPYTmI7ie0ktkFsg9gGsQ1iG8Q2iG0Q2yC2QWyD2AaxDWIbxDaIbRDbILZBbIPYBrENYhvETnI6yekkp5OcTnI6yekkp5OcTnI64HTA6YDTAacDTgecDjgdcDrgdMDpgNMBpwNOB5wOOB1wOuB0wOmA0wGnQ075Yt0kdpPYTWI3id0kdpPYTWI3id0kdkHsgtgFsQtiF8QuiF0QuyB2QeyC2AWxC2IXxC6IXRC7IHZB7ILYBbELYhfELoi9JPaS2EtiL4m9JPaS2EtiL4m9JPZA7IHYA7EHYg/EHog9EHsg9kDsgdgDsQdiD8QeiD0QeyD2QOyB2AOxB2JPzaqhgTZE0IYK2pBBGzpoQwhtKKENKbShhTbF0KYa2pRDm3poUxBtKqJNSbSpiTZF0aYq2kDsz5H/jRahjDal0aY22hRHm+poUx5t6qPN2PJB66rwyvDq8ArxKvFK8WrxivGq8czx7PEM8izyTPJs8ozyrPLM8uzyDPMs80zzbPOM86zzzPPs8wz0LPRM9OjxHUG+o8h3JPmOJt8R5TuqfEeW7+jyXWG+q8x3pfmuNt8V57vqfFee7+rzXYG+q9B3JfquRt8V6bsqfVem7+r0XaG+q9R3pfo/Ry0iNjx3XwDrn1PlaeRp5mnlaefp5Onm6eXpy93HDb+/Y0DHgo4JHRs6RnSs6JjRsaOwo/Q/YEdhR2FHYUdhR2FHYUdhx8COETsqiVUSqyRWSaySWCWxSmKVxCqJFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYiNZDKSyUgmI5mMZDKSyUgmI5mMZDLAZIDJAJMBJgNMBpgMMBlgMsBkgMkAkwEmA0wGmAwwGWAywGSAySCTfIkGXqKBl2gmsZnEZhKbSWwmsZnEZhKbSWwmsQliE8QmiE0QmyA2QWyC2ASxCWITxCaITRCbIDZBbILYBLEJYhPEJohNEJsgtpLTSk4rOa3ktJLTSk4rOa3ktJLTAqcFTgucFjgtcFrgtMBpgdMCpwVOC5wWOC1wWuC0wGmB0wKnBU4LnBY4LXLKF2snsZ3EdhLbSWwnsZ3EdhLbSWwnsQ1iG8Q2iG0Q2yC2QWyD2AaxDWIbxDaIbRDbILZBbIPYBrENYhvENohtENsgdpLTSU4nOZ3kdJLTSU4nOZ3kdJLTAacDTgecDjgdcDrgdMDpgNMBpwNOB5wOOB1wOuB0wOmA0wGnA04HnA44HXLKF+smsZvEbhK7SewmsZvEbhK7SewmsQtiF8QuiF0QuyB2QeyC2AWxC2IXxC6IXRC7IHZB7ILYBbELYhfELohdELsgdkHsJbGXxF4Se0nsJbGXxF4Se0nsJbEHYg/EHog9EHsg9kDsgdgDsQdiD8QeiD0QeyD2QOyB2AOxB2IPxB6IPRB7alYNDbQhgjZU0IYM2tBBG0JoQwltSKENLbQphjbV0KYc2tRDm4JoUxFtSqJNTbQpijZV0QZi398cP49ahDLalEab2mhTHG2qo015tKmPNmPLB62rwivDq8MrxKvEK8WrxSvGq8Yzx7PHM8izyDPJs8kzyrPKM8uzyzPMs8wzzbPNM86zzjPPs88z0LPQM9Gjx//95vhxyzlI8h1NviPKd1T5jizf0eW7wnxXme9K811tvivOd9X5rjzf1ee7An1Xof/+5vh55H+jRcCmTP/9zfHzqEXAplT//c3x04sW4bn7Ajj+OVWeRp5mnlaedp5Onm6eXp6+3H3c8Ps7BnQs6JjQsaFjRMeKjhkdOwo7Sv8DdhR2FHYUdhR2FHYUdhR2DOwYsaOSWCWxSmKVxCqJVRKrJFZJrJJYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgdhIJiOZjGQykslIJiOZjGQykslIJgNMBpgMMBlgMsBkgMkAkwEmA0wGmAwwGWAywGSAyQCTASYDTAaYDDLJl2jgJRp4iWYSm0lsJrGZxGYSm0lsJrGZxGYSmyA2QWyC2ASxCWITxCaITRCbIDZBbILYBLEJYhPEJohNEJsgNkFsgtgEsQliKzmt5LSS00pOKzmt5LSS00pOKzktcFrgtMBpgdMCpwVOC5wWOC1wWuC0wGmB0wKnBU4LnBY4LXBa4LTAaYHTIqd8sXYS20lsJ7GdxHYS20lsJ7GdxHYS2yC2QWyD2AaxDWIbxDaIbRDbILZBbIPYBrENYhvENohtENsgtkFsg9gGsQ1iJzmd5HSS00lOJzmd5HSS00lOJzkdcDrgdMDpgNMBpwNOB5wOOB1wOuB0wOmA0wGnA04HnA44HXA64HTA6YDTIad8sW4Su0nsJrGbxG4Su0nsJrGbxG4SuyB2QeyC2AWxC2IXxC6IXRC7IHZB7ILYBbELYhfELohdELsgdkHsgtgFsQtiF8ReEntJ7CWxl8ReEntJ7CWxl8ReEnsg9kDsgdgDsQdiD8QeiD0QeyD2QOyB2AOxB2IPxB6IPRB7IPZA7IHYA7GnZtXQQBsiaEMFbcigDR20IYQ2lNCGFNrQQptiaFMNbcqhTT20KYg2FdGmJNrURJuiaFMVbSD2/c3x86hFKKNNabSpjTbF0aY62pRHm/poM7Z80LoqvDK8OrxCvEq8UrxavGK8ajxzPHs8gzyLPJM8mzyjPKs8szy7PMM8yzzTPNs84zzrPPM8+zwDPQs9Ez16/N9vjh+3nIMk39HkO6J8R5XvyPIdXb4rzHeV+a4039Xmu+J8V53vyvNdfb4r0HcV+u9vjp9H/jdaBGzK9N/fHD+PWgRsSvXf3xw/vWjRx3P3+38=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Sales", "numbers"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type)
in
#"Inserted Year"

 

 

 

 

Measure: 

Measure =
VAR E =EDATE(MAX('Table(2)'[Date]),-12)

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

'Table(2)',

'Table(2)'[Date]>=S&&'Table(2)'[Date]<=E)

Var result =CALCULATE(SUM('Table(2)'[numbers]),ACTIVE,ALL('Table(2)'[Date]))

RETURN result
 
Measure returns blank: