Can't get the correct total

DAX DAX Calculations
1

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 =
SUMX(
ADDCOLUMNS(
VALUES(Dates[Date]),
“@OL”,
if ( [OL RE] = BLANK(),blank(),
CALCULATE( [OLRE TD after],
Dates[Date] = MaxDate))),
[@OL])

RETURN
Result

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

image

Thanks a lot!
Mariusz

2

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

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

RETURN
IF(
    HASONEVALUE( Dates[Date] ),
    Result,
    SUMX( VALUES( Dates[Date] ), Result )
3

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

4

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)

5

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!

6

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(),
    BLANK(),
    CALCULATE( [OL RE after 12], PREVIOUSDAY( Dates[Date] ) )
) 
VAR MultiDay = [OL RE after 12] 

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

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)

8

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:

9

Thanks. I do appreciate your help.

10

Is this what you want?

1
11204×760 27.9 KB

11

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!

image
image701×262 21.4 KB

WrongTotal_v3.pbix (156.3 KB)

12

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?

13

Hello @mno,

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

Totals under one measure
Totals under one measure945×690 56.7 KB

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 = 
VAR Calc = ADDCOLUMNS(
                VALUES( Dates[Date] ),
                    "@OL",
                IF( ISBLANK( [OL RE] ) ,
                    BLANK() , 
                        CALCULATE( [OL RE after 12] , PREVIOUSDAY( Dates[Date] ) ) ) )

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

RETURN
Result

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

Thanks & Warm Regards,
Harsh

WrongTotal_v2.pbix (151.7 KB)

14

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!

image
WrongTotal_v4.pbix (151.6 KB)

15

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?

16

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: