Forum Discussion

AlexandruP's avatar
AlexandruP
Frequent Visitor
2 years ago

SUM by LAST YEAR VALUE VALUE

Hello,

 

So I have a dimension for my products which is SeasonCode. For example I have SS23, SS22, etc. But each season the SeasonCode changes so we had specific products part of SS22 collection and the new products coming this year are part of SS23 collection. 

 

1 product can only have 1 season code so I am interested to calculate sales of last year for the previous season.

 

So, a measure that can show the sales of the previous season, in this case if I pick with a filter SS23 I see using the measure the sales last year same period of SS22.

I tried having a calculated column which shows fine the previous year season but cannot figure out how to calculate the measure. 

 

Format of season code is always length 4 and 

General Comment
Need Help

2 Replies

  • Igna's avatar
    Igna
    Icon for Resolver III rankResolver III
    2 years ago

    Hello,

     

    Try this :

    Sales for Previous Season =
VAR SelectedSeason = SELECTEDVALUE('YourTable'[SeasonCode])
VAR PreviousSeason = 
    IF (
        LEFT(SelectedSeason, 2) = "SS",
        "SS" & (VALUE(RIGHT(SelectedSeason, 2)) - 1)
    )
RETURN
    CALCULATE(
        [Total Sales], 
        FILTER('YourTable', 'YourTable'[SeasonCode] = PreviousSeason)
    )

     

    Hope it helps

     

    Igna

  • AlexandruP's avatar
    AlexandruP
    Frequent Visitor
    2 years ago

    Hi,

     

    Thanks for the help. Seems it didn't worked but I made it this way and works. However, it doesn't work on aggregations. From what I think is worng is basically I am feeding it a 1 value input so it doesn't work on a list. Problem is I want it to work with multiple values selected at the same time. The below formula works with multiple values selected only if i add the Sæason code rows (so it treats 1 value at a time) but if i want the aggregate it doesn't work. Any ideas how I might do it ? (ex: all the below season codes can be for 1 brand so I am interested in the sales of the whole brand past season codes on an aggregate and not necessarily displayed per season code)

     

    Oms. Index Sæson2 SÅ AW23 = 

VAR SelectedSeason = SELECTEDVALUE(D_Vare[Sæsonkode])
VAR PreviousSeason = 
    IF (
        LEFT(SelectedSeason, 2) = "AW"||LEFT(SelectedSeason, 2) = "CR"||LEFT(SelectedSeason, 2) = "PS"||LEFT(SelectedSeason, 2) = "SS",
        LEFT(SelectedSeason, 2) & (VALUE(RIGHT(SelectedSeason, 2)) - 1),SelectedSeason
    )

VAR Oms  =  CALCULATE(
        SUM(F_Butikssalg[Oms]), SAMEPERIODLASTYEAR(D_Periode[Dato]),
        FILTER(ALL(D_Vare[Sæsonkode]), D_Vare[Sæsonkode] = PreviousSeason)
    )

RETURN

IFERROR(DIVIDE([Oms. kr.],Oms,0)*100,0)