How to get a the sum income total for last week in previous year

[TJHughes]

I need to be able to get the sum of the "This Week Income" column per branch for the last week, or max week, for the previous year selected in the current report. We then use this to compare for YTD differences etc.

The table I am using has data set up as follows:

YEAR	WEEK	THIS_WEEK_BRANCH	THIS WEEK INCOME
2023	25	S11	25
2023	25	S11	10
2023	26	S56	45
2023	26	S56	15
2023	27	S98	85
2023	27	S98	25
2023	28	S42	95
2023	28	S42	45
2023	29	S11	30
2023	29	S11	85
2023	30	S52	5
2023	30	S52	95
2023	31	S77	80
2023	31	S77	30
2023	32	S11	90
2023	32	S11	5
2023	33	S56	100
2023	33	S56	80
2023	34	S98	50
2023	34	S98	90
2023	35	S42	60
2023	35	S42	100
2023	36	S11	70
2023	36	S11	50
2023	37	S52	90
2023	37	S52	60
2023	38	S77	10
2023	38	S77	70
2023	39	S11	15
2023	39	S11	90
2023	40	S56	25
2023	40	S56	10
2023	41	S98	45
2023	41	S98	15
2023	42	S42	85
2023	42	S42	25
2023	43	S11	95
2023	43	S11	45
2023	44	S52	30
2023	44	S52	85
2023	45	S77	5
2023	45	S77	95
2023	46	S11	80
2023	46	S11	30
2023	47	S56	90
2023	47	S56	5
2023	48	S98	100
2023	48	S98	80
2023	49	S42	50
2023	49	S42	90
2023	50	S11	60
2023	50	S11	100
2023	51	S52	70
2023	51	S52	50
2023	52	S77	90
2023	52	S77	60
2023	52	S11	5
2023	52	S56	80
2023	52	S98	90
2023	52	S42	100
2023	52	S11	50
2023	52	S52	60
2023	52	S77	70
2023	52	S11	90
2023	52	S56	10
2023	52	S98	15
2023	52	S42	25
2023	52	S11	45
2023	52	S52	85
2023	52	S77	95
2023	52	S11	30
2023	52	S56	5
2023	52	S98	80
2023	52	S42	90
2023	52	S11	100
2023	52	S52	50
2023	52	S77	60
2023	52	S11	5
2023	52	S56	80
2023	52	S98	90
2023	52	S42	100
2023	52	S11	50
2023	52	S52	60
2023	52	S77	70
2023	52	S11	90
2023	52	S56	10
2023	52	S98	15
2023	52	S42	25
2023	52	S11	45
2023	52	S52	85
2023	52	S77	95
2023	52	S11	30
2023	52	S56	5
2023	52	S98	80
2023	52	S42	90
2023	52	S11	100
2023	52	S52	50
2023	52	S77	60
2024	25	S11	80
2024	25	S11	30
2024	25	S11	85
2024	26	S56	90
2024	26	S56	5
2024	26	S56	95
2024	27	S98	100
2024	27	S98	80
2024	27	S98	30
2024	28	S42	50
2024	28	S42	90
2024	28	S42	5
2024	29	S11	60
2024	29	S11	100
2024	29	S11	80
2024	30	S52	70
2024	30	S52	50
2024	30	S52	90
2024	31	S77	90
2024	31	S77	60
2024	31	S77	100
2024	32	S11	10
2024	32	S11	70
2024	32	S11	50
2024	33	S56	15
2024	33	S56	90
2024	33	S56	60
2024	34	S98	25
2024	34	S98	10
2024	34	S98	70
2024	35	S42	45
2024	35	S42	15
2024	35	S42	90
2024	36	S11	85
2024	36	S11	25
2024	36	S11	10
2024	37	S52	95
2024	37	S52	45
2024	37	S52	15
2024	38	S77	30
2024	38	S77	85
2024	38	S77	25
2024	39	S11	5
2024	39	S11	95
2024	39	S11	45
2024	40	S56	80
2024	40	S56	30
2024	40	S56	85
2024	41	S98	90
2024	41	S98	5
2024	41	S98	95
2024	42	S42	100
2024	42	S42	80
2024	42	S42	30
2024	43	S11	50
2024	43	S11	90
2024	43	S11	5
2024	44	S52	60
2024	44	S52	100
2024	44	S52	80
2024	45	S77	70
2024	45	S77	50
2024	45	S77	90
2024	46	S11	90
2024	46	S11	60
2024	46	S11	100
2024	47	S56	10
2024	47	S56	70
2024	47	S56	50
2024	48	S98	15
2024	48	S98	90
2024	48	S98	60
2024	49	S42	25
2024	49	S42	10
2024	49	S42	70
2024	50	S11	45
2024	50	S11	15
2024	50	S11	90
2024	51	S52	85
2024	51	S52	25
2024	51	S52	10
2024	52	S77	95
2024	52	S77	45
2024	52	S77	15

I tried creating a sumarize table using the following but it is not giving me the correct results:

EOPY = SUMMARIZE(FIGURES,

FIGURES[YEAR],

FIGURES[WEEK NO],

FIGURES[THIS_WEEK_BRANCH],

"INCOME",SUM(FIGURES[THIS WEEK INCOME]))

[TJHughes · Accepted solution]

Fixed it, needed to add the branch to the filter in my code. So it makes it:

Latest Year =

VAR _LatestYear =

            CALCULATETABLE(SUMMARIZE(OR_RORDERINCOMES1,OR_RORDERINCOMES1[YEAR]))

VAR _MaxWeek =

    MAXX(

        FILTER(

            CALCULATETABLE(

                SUMMARIZE(OR_RORDERINCOMES1, OR_RORDERINCOMES1[YEAR],OR_RORDERINCOMES1[WEEK No]) ,

                REMOVEFILTERS()  ),

        OR_RORDERINCOMES1[YEAR] = _LatestYear-1),

    OR_RORDERINCOMES1[WEEK No]  )

VAR _EOYIncome =

    CALCULATE(

        [This Week Income (Nett)], //This week income is just a measure that is SUM(THIS_WEEK_INCOME)/1.2 to remove VAT.

        FILTER( ALL( OR_RORDERINCOMES1),

        OR_RORDERINCOMES1 [YEAR] = _LatestYear -1

            && OR_RORDERINCOMES1 [WEEK NO] = _MaxWeek  ), VALUES(OR_RORDERINCOMES1[THIS_WEEK_INCOME_BRANCH] )  )

RETURN

_EOYIncome

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

income: = 
SUM( FIGURES[INCOME] )

Latest year YTD income vs previous year YTD: =
VAR _latestyear =
    MAXX (
        CALCULATETABLE ( SUMMARIZE ( FIGURES, PERIOD[YEAR] ), REMOVEFILTERS () ),
        PERIOD[YEAR]
    )
VAR _latestweek =
    MAXX (
        FILTER (
            CALCULATETABLE (
                SUMMARIZE ( FIGURES, PERIOD[YEAR], PERIOD[WEEK] ),
                REMOVEFILTERS ()
            ),
            PERIOD[YEAR] = _latestyear
        ),
        PERIOD[WEEK]
    )
VAR _latestyearytd =
    CALCULATE (
        [income:],
        FILTER (
            ALL ( PERIOD ),
            PERIOD[YEAR] = _latestyear
                && PERIOD[WEEK] <= _latestweek
        )
    )
VAR _sameperiodpreviousyear =
    CALCULATE (
        [income:],
        FILTER (
            ALL ( PERIOD ),
            PERIOD[YEAR] = _latestyear - 1
                && PERIOD[WEEK] <= _latestweek
        )
    )
RETURN
    DIVIDE ( _latestyearytd - _sameperiodpreviousyear, _sameperiodpreviousyear )

[TJHughes]

Thanks for reply,

I dont think I have been clear so I have created an example report with some data in. The data I have is a table which has the WEEK (which is YYYY/WW), which we use in report to income for that week. I then have YEAR and WEEK no, which are the WEEK split. The BATCH_DATE is the date that data was collected. The BRANCH is for the branch number it applies too.

What I need to do is see what the weeks income for a branch is for the last week of the previous year selected. So if they select WEEK 2024/50 then the result is for the last week in the data for YEAR 2023, in this case for YEAR 2023 and WEEK No 52 or WEEK 2023/52. We can then use this to get comparisons for each branch on what the income is for the selected week against what it was for the last week in the year before.

I cannot add  a copy of the PBIX file but here is a screen shot of the report visual showing week slicer and table showing list of branches and the week selected income total but I need it to then show the weeks total for the last week of the previous year, that is what I cannot get.

Here is a screenshot of the data table:

I hope that makes sense and is clearer.

[TJHughes]

I have done the following and it calculates the total of the last week of previous year selected but shows the grand total for each row, it is not filtering it by branch?

Measure I have used is:

Latest Year =

VAR _LatestYear =

            CALCULATETABLE(SUMMARIZE(OR_RORDERINCOMES1,OR_RORDERINCOMES1[YEAR]))

VAR _MaxWeek =

    MAXX(

        FILTER(

            CALCULATETABLE(

                SUMMARIZE(OR_RORDERINCOMES1, OR_RORDERINCOMES1[YEAR],OR_RORDERINCOMES1[WEEK No]) ,

                REMOVEFILTERS()  ),

        OR_RORDERINCOMES1[YEAR] = _LatestYear-1),

    OR_RORDERINCOMES1[WEEK No]  )

VAR _EOYIncome =

    CALCULATE(

        [This Week Income (Nett)],

        FILTER( ALL( OR_RORDERINCOMES1),

        OR_RORDERINCOMES1 [YEAR] = _LatestYear -1

            && OR_RORDERINCOMES1 [WEEK NO] = _MaxWeek  )  )

RETURN

_EOYIncome

Just need this to break it down for each branch location as it looks like this currently: