How to add Last Year entire data in the same matrix table not relating to the month selected

Harsh · January 7, 2021, 1:04pm

Before I proceed further I’m not sure how you got the numbers for “FY 2019” as “-4,23,602” and for “FY 2020” as “99,16,970” because when I loaded the file the figures for “FY 2019” and “FY 2020” are “-4,23,136” and “-99,17,115” respectively. And I also compared the results/figures with the formulas/measures that you’ve corrected and they’re absolutely in sync in the file. So please check in your file how the results are derived.

Now, in order to derive/determine the results of “LY Actuals” in case of multiple years it just required the minor change into the and below it’s provided for the reference.

LY Actuals Based on Primary Measure -

 LYTD Actuals - 1 = 
 VAR SelectedYear = SELECTEDVALUE( DateTable[FY] )

 RETURN
 IF( SelectedYear <> SelectedYear , 
     BLANK() , 
     CALCULATE( 
         CALCULATE( [Total Actuals] ,  
             ALL( DateTable[Year] ) ) , 
                 DateTable[FY] = SelectedYear - 1 ) ) * -1

LY Actuals Not Based On Primary Measure -

 LYTD Actuals - 2 = 
 VAR SelectedYear = SELECTEDVALUE( DateTable[FY] )

 RETURN
 IF( SelectedYear <> SelectedYear  ,
     BLANK() , 
     CALCULATE(
         CALCULATE( SUM( Transactions[NetBalance] ) , 
             Transactions[Book] = "MAIN" , 
                 ALL( DateTable[Month] ) , 
                 ALL( DateTable[Year] ) ) , 
                     DateTable[FY] = SelectedYear - 1 ) )

Now, let’s see the results -

For FY 2019, Total YTD Actuals figures is “-4,23,135.82” and the LYTD Actuals figures should be “BLANK” since there’s no data for FY 2018. Below is the screenshot provided for the reference -

For FY 2020, Total YTD Actuals figures is “-99,17,114.55” whereas now the LYTD Actuals figures should be “-4,23,135.82”. Below is the screenshot provided for the reference -

For FY 2021, Total YTD Actuals figures is “-42,33,626.02” whereas now the LYTD Actuals figures should be “-99,17,114.55”. Below is the screenshot provided for the reference -

Also for both the financial years i.e. FY 2020 and FY 2021 you can also check the results by selecting the months from July - December whether the results remain static or not. Below are the screenshots of the results provided for the reference -

So finally, it’s proved that the formula/measure is working absolutely fine.

I’m also attaching the working of the PBIX file for the reference

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

Thanks and Warm Regards,
Harsh

MaskedData - Harsh.pbix (1.7 MB)