Bill comparison

[Anonymous]

I have an Excel spreadsheet where I download my companies phone bill and paste the data each month. The data starts with the phone number in column A, followed by the type of line (e.g. Broadband, Telephone) in column B. Column C contains call volume. 

I start a new worksheet for each new months data.

What I'm hoping I can do is a quick lookup of each phone number in column A and check if it has 0 for call volume across the last 3 months. The phone numbers are not always on the same row each month as lines have different frequencies for billing e.g. some are monthly, some are quarterly. Not sure if that makes a difference....

Is there a way of doing this? I can try and provide a sample of the spreadsheet if needed.

Thanks

[v-xicai]

Hi  @Anonymous ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[v-xicai]

Hi @Anonymous ,

You can try to create measure like DAX below. Or could you please share your sample data or screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Measure1 =CALCULATE(LOOKUPVALUE(Table1[phone number],Table1[call volume],BLANK()),DATEADD(TODAY(),-3,MONTH) <=MAX('DateDim'[Date])&&TODAY()>=MIN('DateDim'[Date]))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Amy,

Thanks for your reply.

This is the kind of spreadsheet i'm working with. It's got three tabs as an example - one for March, one for February and one for January. 

On this one, the phone numbers (column A which is called Service no) are the same on each tab but not on the same row.

Unfortunately my organisation has blocked sharing OneDrive docs to external users, but here are some screenshots. If an email copy is easier, pm me an email address to send it to and I can do that.

Thanks so much for your help 🙂

[tex628]

How do you want the result visualized? 

[Anonymous]

My end goal is to be able to see which numbers have 0 in call volume for 3 months. So i'm open to anything that'll give me that really. 

Thank you

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create new calculated table Table_UNION to union the three month table data, then create measure Filter1 in Table_UNION ,put the Filter1 into the Visual Level Filter of table visual which will display the result ,setting Filter1 as "is not blank".

Table_UNION= UNION(ADDCOLUMNS('January table',"Month","January"),ADDCOLUMNS('February table',"Month","February"),ADDCOLUMNS('March table',"Month","March"))

Filter1= IF(SUM(Table_UNION[CALL VOLUME])=BLANK(),1,BLANK())

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tex628]

Does each page in the excel equal the total volume for that month?