cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnandRanga Member
Member

Comparing revenue of two months in one chart ?

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:

Untitled.jpg

and placed the measures and axis like this:

Untitle3.jpg

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.

1 REPLY 1
v-ljerr-msft Super Contributor
Super Contributor

Re: Comparing revenue of two months in one chart ?

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"

t1.PNG

"Table2"

t2.PNG

1. Use columns from "Table1" and "Table2" for the two Slicers.

s1.PNGs2.PNG

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