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 two measures "Compare From" and "Compare To" in my Fact Table. I need to show the data in the "Compare From" until my selected month. And my Compare To measure should show the data for all the months for the selected Year and it should ignore the month filter.
I have a month and year filter in my report. The relationship with my Date dimension is 1 to Many to my Fact Table.
For example : If my month filter is "May" and Year filter is "2019" then my "Compare From" measure should show the data from January 2019 till May 2019 selected month and year. Here my "Compare To" measure should show the data from Jan 2019 till Dec 2019.
Please help me with the DAX formula.
Regards
SHIVA
Solved! Go to Solution.
Hi @ncbshiva ,
Have uploaded the sample .pbix file for your reference.
Regards,
Yuliana Gu
Hi @ncbshiva ,
Add a calendar table which is not linked to fact table. Add [YearNo] and [MonthName] from 'Date dimension' into slicers.
Date dimension = ADDCOLUMNS ( CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2019, 12, 31 ) ), "YearNo", YEAR ( [Date] ), "MonthName", FORMAT ( [Date], "MMM" ), "MonthNo", MONTH ( [Date] ) )
Then, create measures as below.
Compare from = IF ( SELECTEDVALUE ( 'Fact'[Date].[Year] ) = SELECTEDVALUE ( 'Date dimension'[YearNo] ) && SELECTEDVALUE ( 'Fact'[Date].[MonthNo] ) <= SELECTEDVALUE ( 'Date dimension'[MonthNo] ), SUM ( 'Fact'[Amount] ), BLANK () ) Compare to = IF ( SELECTEDVALUE ( 'Fact'[Date].[Year] ) = SELECTEDVALUE ( 'Date dimension'[YearNo] ), SUM ( 'Fact'[Amount] ), BLANK () )
Best regards,
Yuliana Gu
Hi @v-yulgu-msft ,
I tried in my pbix file it is not working , so i am asking can you please share me the pbix file which you have created these Compare To and Compare From measures.
Regards
SHIVA
Hi @ncbshiva ,
Have uploaded the sample .pbix file for your reference.
Regards,
Yuliana Gu
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |