Can't get the correct total

Hi PBI Masters,

I was wondering if you could help me out.

I can’t get the sum of the Measure 1.
I solved the problem by creating Measure 2.
However, I would like to achieve the result in the measure 1.

Measure 1 =
VAR ActualDate = SELECTEDVALUE( Dates[Date])
VAR MaxDate = CALCULATE( MAX( Dates[Date]), FILTER(all(Dates), Dates[Date] < ActualDate && [OL RE] > 0))

VAR Result =
if ( [OL RE] = BLANK(),blank(),
Dates[Date] = MaxDate))),


Measure 2 =
If (HASONEVALUE( Dates[Date]),
[Measure 1],
[Measure 1]))


Thanks a lot!

normally I would want to see your model to test this - but will something like this work?

Measure 1 =
VAR ActualDate = SELECTEDVALUE( Dates[Date] ) 
    MAX( Dates[Date] ),
    FILTER( all( Dates ), Dates[Date] < ActualDate &&
    [OL RE] > 0 )
VAR Result = SUMX(
        VALUES( Dates[Date] ),
        “ @OL ”,
           [OL RE] = BLANK(),
            CALCULATE( [OLRE TD after], Dates[Date] = MaxDate )

    HASONEVALUE( Dates[Date] ),
    SUMX( VALUES( Dates[Date] ), Result )

SELECTEDVALUE at month, quarter, year and grand total won’t return anything because it checks for a single value. use MAX instead.

Thanks! That was my first attempt, but it is not working :frowning:
Please have a look at the model attached.
WrongTotal.pbix (158.9 KB)

Thanks, but I’m not sure that I understand your comments.
Should I use
VAR ActualDate = Max( Dates [Date] ) ?
I tried, but no success. Thanks anyway!

See if this will work, after looking at your model, I think we can simplify your measure considerably

Test Measure =
VAR SingleDay = IF(
   [OL RE] = BLANK(),
    CALCULATE( [OL RE after 12], PREVIOUSDAY( Dates[Date] ) )
VAR MultiDay = [OL RE after 12] 

IF( ISFILTERED( Dates[Date] ), SingleDay, MultiDay )

Many thanks!
Let me share some thoughts after testing.
The total seems to be not correct and it disappears if the date is filtered.
Can you please think of any other idea?
WrongTotal_v2.pbix (156.3 KB)

okay - the ISFILTERED is what’s causing the totals to vanish once you start filtering the table, returning to your HASONEVALUE solution fixes that part.

But as for the total being wrong, probably the multi-day portion of the formula needs to iterate over a virtual table, as you were using in your first solution.
Putting out a call to those more expert than myself in virtual tables: @BrianJ, @Melissa if either of you has time to weigh in, I think I’ve confused more than helped here :slight_smile:

Thanks. I do appreciate your help.

Is this what you want?

Thanks for your effort.
Numbers in the column “OL RE after 12” should be moved down as in the column “Measure 1”.
This has been achieved, but the total is wrong, therefore I have to create Measure 2 to have the total correct.
The question remains, how to do it in one go?
First, it seems easy, but apparently it’s not :wink: Thanks a lot!

WrongTotal_v3.pbix (156.3 KB)

Now that I think of it you are just trying to get the values from date just prior to to the current cell, 7/3 gets the value from 7/2, 8/2 gets the value of 8/1?

Hello @mno,

I guess this is what you’re trying to achieve.

I’m also attaching the PBIX file for the reference as well as providing a link below where a post has been created by one of our expert member @Greg. He has covered all the aspects about how one can rectify their incorrect totals.

You can furthermore, trim down your measure by eliminating the variables which are not required. Below is the alternative measure provided for the reference -

Measure 1 = 
                VALUES( Dates[Date] ),
                IF( ISBLANK( [OL RE] ) ,
                    BLANK() , 
                        CALCULATE( [OL RE after 12] , PREVIOUSDAY( Dates[Date] ) ) ) )

VAR Result = SUMX( Calc , [@OL] )


Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks & Warm Regards,

WrongTotal_v2.pbix (151.7 KB)

Thanks, but I think that the PREVIOUSDAY does not do the job.
When the next day is without data, the number is not there anymore.
You can see on the screen below that the number “1” is not present in your measure whereas it is present in the original measures.
Can you please think of any other idea? Thanks!

WrongTotal_v4.pbix (151.6 KB)

I will try to explain:
OL RE - volume received on the selected day
OL RE after 12 - volume received on the selected day after 12 (midday)
Volume which is received after 12 should be moved to the next working day.
Would that help?

I couldn’t help myself :slight_smile:
The solution has been found.
Probably is not the best one, but it’s working like a charm.

The final measure - Measure 1_good - you can find in the attached file.
WrongTotal_v5_done.pbix (146.3 KB)

Have a good weekend :wink: