Compare values per hour vs values from the other day

[DJOXXXA]

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:

 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!

[dax]

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]

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.

[dax]

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 · Accepted solution]

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.