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

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