1 ACCEPTED SOLUTION
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 )
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.
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:
