How to get Last year values ignoring Date filter/slicer

[kristel_tulio]

Hi Community, 

I would like to ask for your help.

I want to get my total values for selected values this financial year then adding last year total values but ignoring the quarter and year slicer.

This should be the values for this FY

And I want to get this value for LY comparing them side by side with the selected quarter for this FY in tables

[Mahesh0016 · Accepted solution]

Hello @kristel_tulio 

Total Sale LY =

CALCULATE(

            [Total Sale],PREVIOUSYEAR('Date'[Date])

)

@kristel_tulio  I hope this helps you, Thank You!

[Anonymous · Accepted solution]

Hi @kristel_tulio ,

First of all, many thanks to @Mahesh0016  for your very quick and effective replies, and I will give some additions below:

1.Create the sample table.

2.Create the new measure to calculate FY.

Total_Fy = CALCULATE (
        SUM ('Table'[Values]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] < SELECTEDVALUE('Table'[Year]) || ('Table'[Year] = SELECTEDVALUE('Table'[Year]))
                && 'Table'[Month] <= SELECTEDVALUE('Table'[Month])
        )
    )

3.Create the new measure to calculate LY.

Total_LY = CALCULATE (
        SUM ('Table'[Values]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = (SELECTEDVALUE('Table'[Year]) - 1)
        )
    )

4.Drag the measure into the card visual.

5.Select the year and month. The result is shown below.

Best Regards,

Wisdom Wu

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

[Mahesh0016 · Accepted solution]

Hello @kristel_tulio 

Total Sale LY =

CALCULATE(

            [Total Sale],PREVIOUSYEAR('Date'[Date])

)

@kristel_tulio  I hope this helps you, Thank You!

[kristel_tulio]

Hi @Mahesh0016,

Thank you for responding, but it still not giving me the result I need which ignoring the date slicer.

[Mahesh0016]

@kristel_tulio @Anonymous  have you done the Date table to Mark as a Date table?
If you didn't following :

Step 2 = Create Date Key Column in Date table and Value table
DateKeys = INT(
                           FORMAT( Date[Date],"YYYYMMDD")
                         )
same in value table with value date column.

Step 3 :
 Make relantionship between Value table and Date table using "DateKeys" column.

Step 4 : Create others column in Date table as per your need like Year, Month, etc...

Step 5 : Create Measuers for FY & LY 

Total_Fy = CALCULATE (

        SUM ('Table'[Values]),

        FILTER(

             ALL('Date'),

                'Date'[DateKeys]<=MAX('Date'[DateKeys])

        )

    )

Total Sale LY =

CALCULATE(

            [Total Sale],PREVIOUSYEAR('Date'[Date])

)

Step 6 : Plot slicer and use Date Table column in slicer

>> I have attached a solution file you can also refer to that.

@Anonymous @kristel_tulio I hope you can understand and get your answer following above step.
Thank You! 

[kristel_tulio]

Hi @Mahesh0016 and @Anonymous ,

Thank you both for responding. But I think it won't work with the type of report that I have. But I already figured it out and do work around. I might use your suggested calculation in future. Thanks again for helping out!