Year to Date for Previous Year

[dantheram]

Hi all

i've been working trought this challenge with me data and have nearly cracked it but cannot get the right results

i have an index field in my data which runs back several years (there are 13 periods in each year), this is - 

Index = VALUE ( SUBSTITUTE ( 'SAFs Actuals'[Financial Year], "/", "" ) ) * 100 + 'SAFs Actuals'[Period]

So for this year, 2022/23 - P1 it returns 20222301, P2 is 20222302 etc etc until P13 which is 20222313

what i want to do is compare the running total for 2022/23 against the same time frame for 2021/22, so right now that would be any rows with index >=20222301 <=20222309 for 2022/23

And for 2021/22 (using the same index) it would be any rows >=20212201 <= 20212209

*the page the results are to be displayed on has a filter to the year 2022/23, if this makes any odds?

could someone help me get my head round this one pls?

thanks

Dan 

[dantheram]

hi again

thiis working but for one really annoying bug

RT Actuals LY = 
Var currentmax =
MAX( 'SAFs Actuals'[Index])
  return
    CALCULATE(
        SUM('SAFs Actuals'[Incident Count]),
    ALL('Calendar'[Financial Year]),
     'SAFs Actuals'[Index] >= 20212201,'SAFs Actuals'[Index] <= currentmax -10100)

 - it is returning the number of rows with data and not the sum, so if the there are 4 periods with incidents the 'sum' will be 4 regardless of how many incidents actually occured in the period, as below (see cable faults for good example)

here are the actuall correct values -

[dantheram]

can anyone shed some light on this one - i don't understand why its giving me strange numbers in the table

[dantheram]

kept plugging away and have solved the incorrect numbers but the result is unuseable in any table with a filter on for another year other than the one from which the measure looks at - 2021/22, as it just returns a blank 

TEST YtDLy = 

var _currentmaxperiod =

CALCULATE ( MAX ( 'SAFs Actuals'[Index]), REMOVEFILTERS())

var _lastyrstart =

MAX( 'Calendar'[Index])

Return
    CALCULATE (
     SUM('SAFs Actuals'[Incident Count]),
       filter(
           'SAFs Actuals','SAFs Actuals'[Index]),'SAFs Actuals'[Index] <= _currentmaxperiod -10100 && 'SAFs Actuals'[Index] >= _lastyrstart -10100)

will have to give up now as i think it means i need to rework my data model somehow and i do not have time 😞

[v-yueyunzh-msft · Accepted solution]

Hi , @dantheram 

According to your problem description, you are currently getting the value you want from a measure of a DAX expression operation, but there is an incorrect total row operation in some grouping cases, right?

If this is the case, this is a general problem in Power BI measures, generally caused by the data conversion logic of the measure, the general solution to this problem is to use the Summarize() function to create a virtual table to place the measure, and use the Sumx() function to calculate the correct total value for the measure in the virtual table and display it in the total column, you can check out this blog:

Fixing Incorrect Totals Using DAX Measures In Power BI | Enterprise DNA

If you still have a problem and don't have a solution, please provide a .pbix file that contains your problem without private data.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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