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.
I want to show the revenue of two months in one chart. That two months will be selected by user. For that I provided two filters of months. User selected two months from two different filter and then I calculate the revenue with this expression:
RevenueFromMonth = calculate( sum(TIMESHEETANALYSISCUBE[Total Revenue]), FILTER(TIMESHEETANALYSISCUBE,MONTH((TIMESHEETANALYSISCUBE[TRANSDATE]))=SWITCH([FromValue],"January",01,"February",02,"March",03,"April",04,"May",05,"June",06,"July",07,"August",08,"September",09,"October",10,"November",11,"December",12)))
RevenueToMonth = calculate(sum(TIMESHEETANALYSISCUBE[Total Revenue]), FILTER(TIMESHEETANALYSISCUBE,MONTH((TIMESHEETANALYSISCUBE[TRANSDATE]))=SWITCH([ToValue],"January",01,"February",02,"March",03,"April",04,"May",05,"June",06,"July",07,"August",08,"September",09,"October",10,"November",11,"December",12)))
These are two measures and they are showing the right value of revenue for two different months. Now I took below chart:
and placed the measures and axis like this:
But if I show only one measure It shows the correct value or if I choose only one month it shows the correct value. But If I select both months it shows nothing. It shows blank now If I convert this chart into table it shows me the same value for both the months and because both values of revenue are same It will show me the single line.
I have checked the measures both are showing me the different values , I mean actually values are not same they are different.
needs help, Where I am doing the mistake, Is there any other way to do this.
Thanks for your time.
Hi @AnandRanga,
I want to show the revenue of two months in one chart. That two months will be selected by user. For that I provided two filters of months. User selected two months from two different filter and then I calculate the revenue with this expression:
What columns are you using for the two Slicers, are they two different columns from two different tables, or just the same column of a single table?
If you are using the same column of a single table for the two Slicers, then both "RevenueFromMonth" and "RevenueToMonth" will be filtered by these two Slicers. That's why if you select two different months it shows nothing, and if select a single month it shows the same value for both with a single line.
In this scenario, you need to use two different columns from two separate tables(do make sure there is no any relationship between them) for the two Slicers to make it work. See my sample below.
"Table1"
"Table2"
1. Use columns from "Table1" and "Table2" for the two Slicers.
2. Then use the formulas below for "RevenueFromMonth" and "RevenueToMonth" instead.
RevenueFromMonth = CALCULATE ( SUM ( TIMESHEETANALYSISCUBE[Total Revenue] ), FILTER ( TIMESHEETANALYSISCUBE, MONTH ( ( TIMESHEETANALYSISCUBE[TRANSDATE] ) ) = MAX ( 'Table1'[MonthFrom] ) ) )
RevenueToMonth = CALCULATE ( SUM ( TIMESHEETANALYSISCUBE[Total Revenue] ), FILTER ( TIMESHEETANALYSISCUBE, MONTH ( ( TIMESHEETANALYSISCUBE[TRANSDATE] ) ) = MAX ( 'Table2'[MonthTo] ) ) )
Regards
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |