cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DJOXXXA Regular Visitor
Regular Visitor

Compare values per hour vs values from the other day

Hi all,

 

I have a set of updating values per hour for the todays date and I need to compare these values with the values per hour for the other selected date. Here is what I have so far:

 

1.JPG

 As you can see, I have two slicers, one for current date and one for "previous" date or any other date that should serve as a reference point. In a table, first column is for the hours in a day (will be formatted properly later) and second column is for the values from the first slicer.

Now comes the fun part - column Previous Entries is simple measure to extract the values selected with a second slicer. The measure for this is:

Previous Entries = 
    CALCULATE(
        SUM('TABLE'[Entries]);
            ALL('Date Table');
            USERELATIONSHIP('Date Table'[Date];'Previous Date'[Date])
    )

Now, in order to blank out the values for hours that are yet to happen (from 1500 onwards), I have used the following measure to get the column in the table called Previous Entries Adjusted:

Previous Entries Adjusted = IF(SUM('TABLE'[Entries]) = BLANK();BLANK();[Previous Entries])

As you can see in the picture, this is working fine, except one small issue - the total. Total below the table is stil shown as the full total containing all hours, which is wrong. Instead of 103,010 the total for the Previous Entries Adjusted should be around 36,000. Consequently, the totals for Difference and Variance columns are also wrong.

 

Does anybody have the solution for this problem? Maybe I have used the wrong method to adjust the Previous Entries column?

 

Thank you all in advance for the help!

 

1 ACCEPTED SOLUTION

Accepted Solutions
DJOXXXA Regular Visitor
Regular Visitor

Re: Compare values per hour vs values from the other day

Hi Zoe,

 

I have found the solution yesterday, but forgot to post it here.

Here is what I have done:

1) I have added 3 calculated columns to my dataset:

Max Date Help Column = MAX('TABLE'[date_id])

This column returns the date_id for the last day in the dataset.

Max Hour Help = CALCULATE(MAX('TABLE'[time_id]);FILTER('TABLE';'TABLE'[date_id] = 'TABLE'[Max Date Help Column]))

 This column returns the last available hour in the dataset

Hour Help 2 = IF('TABLE'[Max Hour Help]<'TABLE'[time_id];"Future";"Past")

This column determines whether the hour for previous days is lower or higher than the current latest hour and returns "Future" or "Past".

2) I have modified my measure to be the following:

Previous Entries Short = 
    CALCULATE(
        SUMX('TABLE';'TABLE'[Entries]);
            ALL('Date Table');
            USERELATIONSHIP('Date Table'[Date];'Previous Date'[Date]);
            'TABLE'[Hour Help 2] = "Past"
    )

So, the measure is looking to sum values for the second available slicer (this is why the USERELATIONSHIP part of the measure is used), and then it filters the results to only show "Past" hours (hours that are lower than the current maximum hours). I am sorry for taking your time, but it looks easy now that I have found the solution.

 

If you need any additonal info or pictures, please let me know.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Compare values per hour vs values from the other day

Hi DJOXXXA,

It seems that you need to redefine the grant total  expression  for measure(you could sumx(summarize(),[measure]) to see whether it work or not). If possible, could you please inform me more detailed information(such as your sample data or pbix file and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

DJOXXXA Regular Visitor
Regular Visitor

Re: Compare values per hour vs values from the other day

Hi dax,

 

Thank you for the proposition, but unfortunately it is not working...

Also, I am unable to send you the pbix file since i am doing a direct query to the company server. To explain the problem in more detail - I need a measure or a calculated column to help me filter my data for previous days to show data only until the max hour for current day, data is structured like:

date_id  | time_id | Value
20140101 | 1700    | 5
20140102 | 1800    | 25
20140103 | 2000    | 40

I have a connected date table and a second inactive date table for the second slicer. If you need any other info about measures I have used, I will gladly share.

Community Support Team
Community Support Team

Re: Compare values per hour vs values from the other day

Hi DJOXXXA, 

To reproduce your design ,  need to check something with you: 

You siad that" I need a measure or a calculated column to help me filter my data for previous days to show data only until the max hour for current day," Did you mean you want to get days smaller than today, and the hour need to samller tahn current hour(for example, now= 2019/12/9 16:00, you want to show date from start till to 2019/12/8 16:00, right?). In addition, will you want to calculate cumulative value? or just show value in Table? If possible, could you please inform me more details? Then I will help you more correctly.

Best Regards,
Zoe Zhi

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

 

DJOXXXA Regular Visitor
Regular Visitor

Re: Compare values per hour vs values from the other day

Hi Zoe,

 

I have found the solution yesterday, but forgot to post it here.

Here is what I have done:

1) I have added 3 calculated columns to my dataset:

Max Date Help Column = MAX('TABLE'[date_id])

This column returns the date_id for the last day in the dataset.

Max Hour Help = CALCULATE(MAX('TABLE'[time_id]);FILTER('TABLE';'TABLE'[date_id] = 'TABLE'[Max Date Help Column]))

 This column returns the last available hour in the dataset

Hour Help 2 = IF('TABLE'[Max Hour Help]<'TABLE'[time_id];"Future";"Past")

This column determines whether the hour for previous days is lower or higher than the current latest hour and returns "Future" or "Past".

2) I have modified my measure to be the following:

Previous Entries Short = 
    CALCULATE(
        SUMX('TABLE';'TABLE'[Entries]);
            ALL('Date Table');
            USERELATIONSHIP('Date Table'[Date];'Previous Date'[Date]);
            'TABLE'[Hour Help 2] = "Past"
    )

So, the measure is looking to sum values for the second available slicer (this is why the USERELATIONSHIP part of the measure is used), and then it filters the results to only show "Past" hours (hours that are lower than the current maximum hours). I am sorry for taking your time, but it looks easy now that I have found the solution.

 

If you need any additonal info or pictures, please let me know.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors