Hide Selection but actually use data

[jzhao]

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 visual should display sales data for both FY23 and FY22. However, what happens is that only the FY23 numbers are displayed, and FY22 appears blank. To have both FY23 and FY22 numbers displayed, I need to select both FY23 and FY22.

Is there a way to customize the visualization so that selecting FY23 will display FY23 and FY23-1, and selecting FY22 will display FY22 and FY22-1?

Thank you.

[FreemanZ]

hi @jzhao ,

Try to feed the FY slicer with an unrelated new table and reference the captured value with SELECTEDVALUE in your measure.

it seems the slicer is both selecting the FY23 and filtering your measures. Blank means there is nothing satisfies both FY22 and FY23. 

So try to 1) feed the slicer with an isolated new table; 2) or remove FY filter context in your measure - this could be complicated. 

check this for more info:

https://www.daxpatterns.com/parameter-table/

[jzhao]

Upon further checking, the fiscal year slicer changes the source data used in measures. Is there a way to change its behavior so that instead of selecting data for FY23, it selects data from the beginning to FY23?

[FreemanZ]

yes,  by removing FY filter context in your measure.

how does your measure look like?

[Jack007]

My measure calculates sales for FY22

Last year sales =

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

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

   'SALES TABLE',

   'SALES TABLE'[01 PostingDate]>=S&&'SALES TABLE'[01 PostingDate]<=E&&'SALES TABLE'[DataSource]="ACT")

Var result =CALCULATE(SUM('SALES TABLE'[Sales RESTATE]),ACTIVE)/'Display unit'[Display unit selection]

RETURN result

When I make slicer equal to FY23, it changes the datasource to FY23, removing all sales data in FY22, resulting in blank value Last year sales

[FreemanZ]

hi @Jack007 ,

try like:

Last year sales =

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

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

   'SALES TABLE',

   'SALES TABLE'[01 PostingDate]>=S

        &&'SALES TABLE'[01 PostingDate]<=E

        &&'SALES TABLE'[DataSource]="ACT"

)

Var result =

CALCULATE(

    SUM('SALES TABLE'[Sales RESTATE]),

    ACTIVE,

    ALL('01 Date Table'[Date])

)/'Display unit'[Display unit selection]

RETURN result

[Jack007]

It does not work.

I even tried this:

Last year sales =

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

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

   'SALES TABLE',

   'SALES TABLE'[01 PostingDate]>=S

        &&'SALES TABLE'[01 PostingDate]<=E

        &&'SALES TABLE'[DataSource]="ACT"

)

Var result =

CALCULATE(

    SUM('SALES TABLE'[Sales RESTATE]),

    ACTIVE,

    ALL('01 Date Table'[Date]),

    ALL('SALES TABLE'[Date])

)/'Display unit'[Display unit selection]

RETURN result

[FreemanZ]

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

[jzhao]

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: