Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |