How Can I solve this Problem with the new addes Column Year Previous Year

[Anonymous]

Hello i try to understand why this is not working as I intent.
I am new and try to learn power bi pls be patient with me and if u are kind explain how and why and maybe how I could fix this.
For this example I copied a part of the Data into Excel to do screenshots here.

Ich have this Table, have to blur it cause sensitive Data:

I used this Formular to get this:

Table.AddColumn(
#".......",
"Profit Previous Year",
each let
CurrentDate = [Date],
FilteredTable = Table.SelectRows(#".....", each [Date] = Date.AddYears(CurrentDate, -1))
in
if Table.IsEmpty(FilteredTable) then null else FilteredTable{0}[#"Profit"])

What it should do is:
- getting a new Column "Profit Previous Year" and taking the information from Column "Profit" but -1 Year and it is working in the first Screenshot.
But......

In the second Screenshot u see that it starts all over again like in the first screenshot the 6954143 is the same Profit number as the first one. And this is data like 10k rows it just repeading to start all over again.
What in D66 should be is the Data in C54.


What can I do to solve this?


Kind Regards

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Column:

Previous Profit =
CALCULATE ( SUM ( 'Table'[Profit] ),
    FILTER ( 'Table',
        YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) - 1
            && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Could you give me an example with the column Shop 1 included? Cause I do have more than 30 Shops names and I think I need to implement these because I got the same number all over again

I am not sure what I am doing wrong

[v-zhangti]

Hi, @Anonymous 

Column:

Previous Profit = 
CALCULATE ( SUM ( 'Table'[Profit] ),
    FILTER ( ALLEXCEPT('Table','Table'[Shop]),
        YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) - 1
            && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Hi, @v-zhangti
Thank you so far. It seems looking better now, but 1 think I am missing something or I dont see why.

The list seems doing what I want. But why is the Value not the same? Does it have to do with SUM ? The Value on the right is not in mein Source Excel and I don´t know where this came from.
If I Use ur Code on my example Table I got it all right. Its how I want it. But not on my Main Table.

Another Question!
Can I build this Code of yours in Power Query Editor ? 

How Do I have to change this:

Previous Profit = 
CALCULATE ( SUM ( 'Table'[Profit] ),
    FILTER ( ALLEXCEPT('Table','Table'[Shop]),
        YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) - 1
            && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
    )
)

to implement it here 

Thank you Sir/Ma´am

[Anonymous]

I think I can´t use measures for slicers right?
Thats why I don´t need a measure I need to do it as an column.
Or is it possible then how?

[Uzi2019]

Hi @Anonymous 
You can try craeting measure for Numeric field. If you want to calaculate last year sales then please write below dax;
Prev Sales= CALCULATE(SUM('Table'[Sales]),DATEADD('Calendar'(Date),-1,Year))

OR

Prev Sales=CALCULATE(SUM(Table[Sales]),SAMEPERIODLASTYEAR('Calendar'[Date]))

You will get the same result from both the measures.