Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jzhao
Frequent Visitor

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 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.

8 REPLIES 8
FreemanZ
Super User
Super User

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/

 

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?

yes,  by removing FY filter context in your measure.

 

how does your measure look like?

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

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

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

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

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:

jzhao_0-1698807700287.png

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.