Double Data - how to correct this?

[MrsPickles]

Hi,

I have a Google Analytics property where screenPageviews were incorrectly collected twice for a month (July 2023).

I'd like to write a measure for this that divides any data collected in July 2023 by 2. Can this be done?

Sum of sessions	Year	Month	Sum of screenPageViews
10369	2022	December	14767
38932	2023	January	57066
41814	2023	February	60988
45078	2023	March	70015
37282	2023	April	57623
46590	2023	May	71989
47625	2023	June	72501
44855	2023	July	161193
44424	2023	August	95980
40092	2023	September	56805
46453	2023	October	64432
48868	2023	November	66279
37959	2023	December	52305
49857	2024	January	69268
18078	2024	February	24514

[MrsPickles]

Thanks for both your help. I went with the following after I received more info from the data owner. Double data collected from July 23 to 10 August 23 so I needed to identify each day.

In my date column (dd/mm/yyy) I extraced to a new column day of the month (PVDay), month name (PVMonth) and Year (PVYear).

Then I just nested a bunch of IF statements. There's probably a much cleaner way tro do this bit I couldn't get DATEBETWEEN to work and needed to move on:

Pageviews =

IF(

    'Table'[Year] = 2023 && 'Table'[PVMonth] = "July",

    'Table'[screenPageViews] / 2,

    IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 1,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 2,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 3,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 4,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 5,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 6,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 7,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 8,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 9,

        'Table'[screenPageViews] / 2,

        IF(

        'Table'[Year] = 2023 && 'Table'[PVMonth] = "August" && 'Table'[PVDay] = 10,

        'Table'[screenPageViews] / 2,

    'Table'[screenPageViews])

))))))))))

[Anonymous · Accepted solution]

Hi @MrsPickles ,

If you only need to modify the data in the column Sum of screenPageViews, you can try this:

New = 
IF(
    'Table'[Year] = 2023 && 'Table'[Month] = "July",
    'Table'[Sum of screenPageViews] / 2,
    'Table'[Sum of screenPageViews]
)

The final output is as below:

But if you need to modify multiple columns of data, that may be more convenient in Power Query.

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

[Daniel29195]

@MrsPickles 

you have multiple waya of dealing with this : 
you can use switch in the measure : 

sumx( 

Addcolumns ( 

summarize( 

tbl_name , 

tbl_name[year] , 

tbl_name[month] 

) , 

"@sessions" , 

var y = tbl_name[year] 

var m = tbl_name[month] 

return

switch ( 

true(), 

Y = 2023 && m = july , calculate(sum(sessions))/2 , 

calculate(sum(sessions) ) 

)) , [@sessions] 

)

you can divide all the rows where 2023 july by 2 in power query for all the facts 

let me know if it works for you. 

if it does, kindly mark it as the accepted solution as this will help others in the community find it more easily.