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

[ammu]

Hi Team,
I have data from transactions. we then calculated MTD, YTD and LY as attached.
When I try to show them on Matrix, I have MTD and YTD correct as per the Month selected. Last Year figure is also correct as by LY MTD. However we want the entire LY figure not MTD LY figure.

I can only do with 2 Matrixes overlapping. It is not a good way for drill down function.
Please let me know how to reconstruct it into one proper matrix.

Thank you.
Regards,
Aye

MaskedData.pbix (1.2 MB) MaskedData.xlsx (1.3 MB)

[Harsh]

Hello @ammu,

Thank You for posting your query onto the Forum.

Well it was a minor fix into the formula of “LY ACT” just had to ignore the month context which coming through the slicers part and then we’re able to achieve the desired result into the single matrix itself.

Below is the formula as well as the screenshot of the result provided for the reference -

LY Act = 
CALCULATE( SUM( Transactions[NetBalance] ) , 
    Transactions[Book] = "MAIN" , 
    ALL( DateTable[Month] ) )

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.2 MB)

[ammu]

Hi Harsh,
Thank you so much for replying me so promptly.

My bad that the formulae for LY Act should be read as
LY Act = CALCULATE(SUM(Transactions[NetBalance]), Transactions[Book]=“MAIN”, DATEADD(DateTable[Date],-1,YEAR))

I will require a DAX formula for only last year Actual not interacting to the Month slicer but interacting with FY slicer. I have attached my working for reference.

Hope it makes sense.
thank you.
Regards,
Aye
MaskedData.pbix (1.2 MB)

[ammu]

Hi Harsh and Team,
My sincere apology for bothering you again. can you please let me know if you can update me with my issue.
thank you for your time.
regards,
Aye

[Harsh]

Hello @ammu,

Firstly, I would like to apologize to you for not getting back early to you since I got stuck up in my work.

And finally here’s some good news about the result that you’re looking for. Below is the formula as well as the screenshot of the final output provided for the reference -

Total Actuals = 
CALCULATE( SUM( Transactions[NetBalance] ) , 
    Transactions[Book] = "MAIN" ,
        ALL( DateTable[Month] ) ) * -1




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

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

Now, you’ll observe that inspite of slicer selection for Month the result is absolutely static the way you wanted to see.

Lastly before I sign off from this post, there are certain recommendations that I would like to make to you as far as formula building is concerned -

1. Always build the primary formulas and then go for the measure branching teachnique or variable technique (provided you’re good with it). Right now, I’ve observed that you don’t have a single primary formula in your file and have been using the same set of conditions again and again in all the formula’s that you’ve build. That is -

You can simply create “Total Actuals” and “Total Budgets” formula like this -

Total Actuals = 
CALCULATE( SUM( Transactions[NetBalance] ) , 
    Transactions[Book] = "MAIN" ,
        ALL( DateTable[Month] ) ) * -1


Total Budgets = 
CALCULATE( SUM( Transactions[NetBalance] ) , 
    Transactions[Book] = "SCC_BUDGET" ) * -1

And now, based onto this primary formula, you can simply create the MTD and YTD formulas.

2. The reason behind why I strongly recommend to build the primary measures is because the way your formula was structured to calculate the LY Actuals was like this -

    LY Act = 
    CALCULATE( SUM(Transactions[NetBalance] ) , 
        Transactions[Book]= "MAIN" , 
            DATEADD( DateTable[Date] , -1 , YEAR ) )
   

Whereas the actual formula, should have been like this if you don’t want to build the primary measures -

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

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

Final Output between the formulas - “LY Act V/s LYTD Actuals - 1 V/s LYTD Actuals - 2”

Now, if you observe the formula, you’ll see that how complicate the formula gets, if you don’t build your primrary measures. If you compare the formula with mine you’ll be saved from writing 3 additional lines everytime because I’ve already mentioned this repeating set of conditions into our primary formula.

I’m also attaching the working of the PBIX file for the reference. And I’ve also created a separate measure folder which covers the formulas that I’ve created.

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

Thanks and Warm Regards,
Harsh

MaskedData - Harsh - Final Output.pbix (1.2 MB)

[ammu]

OMG, Harsh.
I cannot thank enough for your guidance.
I did not mean to make you apologies. I was a bit desperate yesterday.
Thank you soo much for directing me to primary formula to branching out.
Apparently I will get to have more practice and apply your solution with my LIVE data.

I will get back to you shortly.

Regards,
Aye

[Harsh]

Hello @ammu,

You’re Welcome.

I’m glad that I was able to assist and guide you through.

Thanks and Warm Regards,
Harsh

[ammu]

Hi Harsh,
Thanks for your reply.
I do not mind having primary formula then branching out with LY Actuals -1.
I am now applying to my LIVE data and the result I am expecting is not quite right.
My issues are

1. I have more than 2 FY in LIVE data.
2. Even in FY 2021, I would like to chose Month ‘Jul’ and I still want to get entire LY actual which is $4,683,301.
3. If I choose FY 2020, I should not get any in theory but I still get $4,683,301 which is incorrect for FY 2019 which I do not have data.
   Hope we can address those issues with the updated DAX.
   Thank you so much for your time.

Regards,
Aye

[Harsh]

Hello @ammu,

Can you please add 2019 masked data as well? So that I can test the formula. Also in that, can you also please provide the description that where the issue is?

Thanks and Warm Regards,
Harsh

[Harsh]

Hello @ammu,

Here’s the explanation for Point No.2 and 3 -

Point No. 2 - Even in FY 2021, I would like to chose Month ‘Jul’ and I still want to get entire LY actual which is $4,683,301.

The reason behind why it was not results for the Month “Jul” is because you had the date table range till 30th June, 2021. So now, when I extended the range till 31st Jul, 2021 it provides the results as you’re expecting. Below is the screenshot provided for the reference -

Point No. 3 - If I choose FY 2020, I should not get any in theory but I still get $4,683,301 which is incorrect for FY 2019 which I do not have data.

The reason behind why it was giving you the results for FY 2019 upon slicer selection of FY 2020 is because if you observe the formula I’ve included the condition as “ALL( DateTable[Month] )” in my formula in order to derive/determine the entire previous years results without providing the results in monthly proportions as per the slicer selection which you were getting as per the “LY Act” formula. So the only solution to this problem is, if you don’t have the data for the Year 2019 than there’s absolutely no point to load the dates for that particular period.

So now, I’ve deleted the Dates pertaining to the period 2019 so that it doesn’t provide the ambiguous results.

So finally, since your FY is from July - June. For FY - 2020, the results will be as per the screenshot provided below -

And for FY - 2021, whether you select the period from Jan - June or from July - Dec it’ll show you the results for the entire previous year. Below is the screenshot provided for the reference -

I’m also attaching the Excel as well as the PBIX file for the reference.

Hoping you find this useful and helps you.

Note: So far I haven’t traced any error into my primary formulas based onto the points suggested. It was the problem within the data itself

Thanks and Warm Regards,
Harsh

MaskedData - Harsh.xlsx (1.3 MB)

MaskedData - Harsh.pbix (1.2 MB)

[ammu]

Hi Harsh,
I now have attached the data for FY 2019 as well as entire year for FY 2020.
If I select FY 2020 and select any month, I need to get the FY2019 Jun YTD result (FY2019 entire year result- which is -423,602 under LY Act column .

If I select FY 2021 and select any month, I am expecting to get FY2020 Jun YTD result (FY2020 entire year result- which is $-9,916,970 under LY Act column.

I have loaded FY 2019, FY 2020 and FY 2021 data for more testing.

Thank you.
MaskedData.pbix (2.1 MB) MaskedData.xlsx (4.0 MB)

[Harsh]

Hello @ammu,

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.

1. 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
   

2. 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 -

1. 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 -

2. 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 -

3. 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)

[ammu]

Many thanks, Harsh.
I have applied in LIVE data and I got what I expected with or without primary formula.
so much appreciated for your time.

Regards,
Ammu

[Harsh]

Hello @ammu,

You’re Welcome.

I’m really glad that were able to see this through and you found it helpful.

Thanks and Warm Regards,
Harsh

[EnterpriseDNA]

Hi @ammu, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!