How to manage values from an actual hour and the values from one or two hours before and substract

[manvpbi]

Hello everyone, Im getting frustrated with this topic. I want to create a visualization like figure 1. where the grey part is a sum value through hours (text type), that represent how much the business grows in hours, the red part represents the difference between the value of an specific hour and the hour before. Example: at 9am Sales = 10$ and 7am Sales = 7$ then your red value will be 3$

Figure 1

Figure 2.

Dataset example

FYI, I have hourly cuts like (6am - 7am - 9am - 11am -12pm - 1pm - 3pm - 5pm - 7pm - 8pm and 10pm)

Date(int)                 ProductID     Hour(Text)         Sales

I already read a lot of information and tried a lot of distinct formulas (dax/ power query) but I didnt have the expected results.

Hope you can help me a bit with this .

Greetings

[manvpbi]

Here some data examples

FK_DATE (int)	HOURS(text)	#VOL(int)
20220311	06:00	208
20220314	06:00	45
20220315	06:00	0
20220316	06:00	268
20220317	06:00	76
20220318	06:00	195
20220319	06:00	114
20220325	06:00	133
20220401	06:00	5357
20220405	06:00	87
20220406	06:00	85
20220407	06:00	71
20220408	06:00	51
20220409	06:00	89
20220411	06:00	59
20220412	06:00	131
20220413	06:00	156
20220414	06:00	161
20220415	06:00	265
20220416	06:00	188
20220418	06:00	22
20220419	06:00	213
20220420	06:00	323
20220421	06:00	357
20220422	06:00	151
20220423	06:00	146
20220428	06:00	145
20220429	06:00	230
20220430	06:00	1171
20220503	06:00	233
20220504	06:00	54
20220505	06:00	33
20220507	06:00	798
20220509	06:00	119
20220510	06:00	1428
20220511	06:00	93
20220512	06:00	48
20220513	06:00	210
20220514	06:00	117
20220516	06:00	104
20220517	06:00	52
20220519	06:00	37
20220520	06:00	92
20220521	06:00	118
20220523	06:00	46
20220524	06:00	255
20220525	06:00	358
20220526	06:00	132
20220506	07:00	77
20220423	09:00	1582
20220428	09:00	819
20220429	09:00	513
20220430	09:00	1171
20220503	09:00	1344
20220504	09:00	251
20220505	09:00	154
20220506	09:00	202
20220507	09:00	978
20220509	09:00	229
20220510	09:00	1476
20220511	09:00	567
20220512	09:00	714
20220513	09:00	354
20220514	09:00	315
20220516	09:00	410
20220517	09:00	255
20220518	09:00	136
20220519	09:00	347
20220520	09:00	186
20220521	09:00	264
20220523	09:00	116

[v-zhangti · Accepted solution]

Hi, @manvpbi 

You can try the following methods.

Measure:

Sum = CALCULATE(SUM('Table'[#VOL]),FILTER(ALL('Table'),[FK_DATE]=SELECTEDVALUE('Table'[FK_DATE])))

Difference =
CALCULATE (
    SUM ( 'Table'[#VOL] ),
    FILTER (
        ALL ( 'Table' ),
        [FK_DATE] = SELECTEDVALUE ( 'Table'[FK_DATE] )
            && [HOURS] = MAX ( 'Table'[HOURS] )
    )
)
    - CALCULATE (
        SUM ( 'Table'[#VOL] ),
        FILTER (
            ALL ( 'Table' ),
            [FK_DATE] = SELECTEDVALUE ( 'Table'[FK_DATE] )
                && [HOURS] = MIN ( 'Table'[HOURS] )
        )
    )

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

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

[manvpbi]

Thanks for your answer @v-zhangti, I did some changes to your measures and I got the results.

MEASURE#1

CALCULATE(SUM([VOLUMEN]),
FILTER(ALL('TABLE'),[FK_DATE]=SELECTEDVALUE('TABLE'[FK_DATE]) && 'TABLE'[HOUR] = SELECTEDVALUE('TABLE'[HOUR])))

MEASURE#2

var ACTUAL_HOUR = SELECTEDVALUE('TABLE'[HOUR])

var PAST_HOUR =

calculate(MAX('TABLE'[HOUR]),

FILTER

(ALLselected('TABLE'),

[HOUR]< ACTUAL_HOUR))

var MAX_VALUE =

CALCULATE (

SUM ( 'TABLE'[VOLUMEN]),

FILTER (

ALL('TABLE'),

'TABLE'[FK_DATE] = SELECTEDVALUE ( 'TABLE'[FK_DATE] )

&& 'TABLE'[HOUR] = ACTUAL_HOUR

)

)

var MIN_VALUE =

CALCULATE (

SUM ( 'TABLE'[VOLUMEN]),

FILTER (

ALL( 'TABLE'),

[FK_DATE] = SELECTEDVALUE ('TABLE'[FK_DATE])

&& 'TABLE'[HOUR] = PAST_HOUR

)

)

var DIFF = MAX_VALUE - MIN_VALUE

return

Diff

[Whitewater100]

Hi:

Can you share some example data? Can be image of your data model and pasting excel right into your reply?

Thanks..