topic Re: Issues with comparing data using date ranges in DAX Commands and Tips

Issues with comparing data using date ranges

fernamario — Fri, 23 Feb 2024 17:22:08 GMT

I am new to PowerBI and defenitely not an expert or developer. I was able to create a report based on an excel file which contains server names, CPU - Avail memory and % of avail memory, date and time. I need to be able to compare utilization between Feb 2 - 16 and then from Feb 20 - March 1 to show any utilization spikes after adding a new customer. I have tried using a Measure and an If statement with no luck. The excel sheet contains:

server name
date time
cpu
avail mem
% of avail mem

it has an hour entry (24 in one day).

Any advice? Thank you in advance.

Re: Issues with comparing data using date ranges

lbendlin — Sat, 24 Feb 2024 01:18:48 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Issues with comparing data using date ranges

Anonymous — Mon, 26 Feb 2024 07:31:48 GMT

Hi fernamario ,

Based on your description, I understand utilization as the "% of avail mem" field. The following data was created:

1. Create a measure, write an expression, calculate the average utilization of the two dates, and then compare.

Measure = VAR DateRange1 = FILTER(ALL('Table'),'Table'[date time] >= DATE(2024,2,2) && 'Table'[date time] <= DATE(2024,2,16)) VAR Utilization1 = CALCULATE(AVERAGE('Table'[% of avail mem]), DateRange1) VAR DateRange2 = FILTER(ALL('Table'),'Table'[date time] >= DATE(2024,2,20) && 'Table'[date time] <= DATE(2024,3,1)) VAR Utilization2 = CALCULATE(AVERAGE('Table'[% of avail mem]), DateRange2) RETURN IF(Utilization1 >= Utilization2,Utilization1,Utilization2)

2. The final result is shown in the figure below.

If my understanding is wrong, please provide more detailed pbix files or screenshots to better help you solve the problem.

Best Regards,

Clara Gong

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

Re: Issues with comparing data using date ranges

fernamario — Mon, 26 Feb 2024 14:16:03 GMT

Clara, thank you so much for this. One question, if I wanted to run this for each server, what is needed in the Measure?

I changed the values for % of avail mem to CPU and Avail Mem for different measures.

Re: Issues with comparing data using date ranges

fernamario — Mon, 26 Feb 2024 20:36:52 GMT

Clara, unfortunately, the results are skewed after I applied the measure. Would it be easier if I attached the files for your review?

Re: Issues with comparing data using date ranges

Anonymous — Tue, 27 Feb 2024 01:24:43 GMT

Hi fernamario ,

Yes, if you upload the file and provide the desired effect, I will understand you better and help you get the desired result. If your data volume is relatively large or has private data, you can create some representative data yourself for easier understanding.

Best Regards,

Clara Gong

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

Re: Issues with comparing data using date ranges

fernamario — Tue, 27 Feb 2024 13:32:43 GMT

Clara, unfortuantely, I cannot upload the file since the org blocks the use of any external cloud storage. Any other options? Let me see if I can use my home PC and personal account to upload.

Re: Issues with comparing data using date ranges

fernamario — Tue, 27 Feb 2024 13:40:12 GMT

Here are both files.

https://docs.google.com/spreadsheets/d/165l1GNqGB4HTymSQDnrFOtlzCZAlkcIU/edit?usp=sharing&ouid=118372175940774225300&rtpof=true&sd=true

https://drive.google.com/file/d/1e48McKzwaAXxgtndeii5N2H7LgeQA5k2/view?usp=sharing

Re: Issues with comparing data using date ranges

lbendlin — Wed, 28 Feb 2024 02:23:06 GMT

The % of mem is redundant.

Note the blue "i" - that is a (bad) indicator that not all data is shown. If you want to avoid the automatic sampling yu need to bring your own.

Re: Issues with comparing data using date ranges

fernamario — Wed, 28 Feb 2024 12:38:54 GMT

Ibendlin, thank you for your response. However, I dont understand what you mean by " If you want to avoid the automatic sampling yu need to bring your own. "

Re: Issues with comparing data using date ranges

lbendlin — Wed, 28 Feb 2024 13:08:58 GMT

You need to make your own decision on how to aggregate the data, or else Power BI will make that decision for you.