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

yes,  by removing FY filter context in your measure.

 

how does your measure look like?

Jack007's avatar
Jack007
Frequent Visitor
2 years ago

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's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Jack007
      Frequent Visitor
      2 years ago

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