Ignore the Month filter for one visual

[ncbshiva]

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.

Month	Compare From	Compare To
January	2747	74647
February	6263	94737
March	5656	51553
April	3434	98589
May	4556	6663
June	567	12772
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.

Month	Compare From	Compare To
January	2747	74647
February	6263	94737
March	5656	51553
April	3434	98589
May	4556	6663
June		12772
July		8874
August		8373
September		8781
October		435
November		827
December		7363

Please attach the PBIX file also.

Regards

SHIVA

[v-frfei-msft · Accepted solution]

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