Forum Discussion

ncbshiva's avatar
ncbshiva
Icon for Advocate V rankAdvocate V
7 years ago
Solved

Ignore the Month filter for one visual

Hi Team,

 

I have Year and Month filters  in my report from Date Dimension. And i have simple table chart showing "Compare From" and "Compare To" measures along the month.

Relationship is setup between my Fact table and date dimension with many to 1 relationship.

Consider the below as my sample data.

MonthCompare FromCompare To
January274774647
February626394737
March565651553
April343498589
May45566663
June56712772
July 8874
August 8373
September 8781
October 435
November 827
December 7363

 

For Example : If i select Month as "May" and Year as "2019" then my table should show me below output.

My "Compare From" measure should show me the data until the selected month that is from Jan 2019 until May 2019. And my "Compare To" measure should show me the data for all the months in Year 2019 and it should ignore the Month filter applied.

 

MonthCompare FromCompare To
January274774647
February626394737
March565651553
April343498589
May45566663
June 12772
July 8874
August 8373
September 8781
October 435
November 827
December 7363

 

Please attach the PBIX file also.

 

Regards

SHIVA

 

 

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi ncbshiva ,

     

    One sample for your reference. Please check the following steps as below.

     

    1. Insert an index column in power query.

    2. To create a calculated table as below.

     

    Table = SELECTCOLUMNS(Table1,"month",'Table1'[Month],"Monthno",Table1[Index])

    3. Then we can achieve our goal by this measure.

    new compare from = 
VAR mon =
    SELECTEDVALUE ( 'Table'[Monthno] )
RETURN
    CALCULATE (
        SUM ( Table1[Compare From] ),
        FILTER ( 'Table1', Table1[Index] <= mon )
    )

     

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi ncbshiva ,

     

    One sample for your reference. Please check the following steps as below.

     

    1. Insert an index column in power query.

    2. To create a calculated table as below.

     

    Table = SELECTCOLUMNS(Table1,"month",'Table1'[Month],"Monthno",Table1[Index])

    3. Then we can achieve our goal by this measure.

    new compare from = 
VAR mon =
    SELECTEDVALUE ( 'Table'[Monthno] )
RETURN
    CALCULATE (
        SUM ( Table1[Compare From] ),
        FILTER ( 'Table1', Table1[Index] <= mon )
    )