Forum Discussion

AlexandruP's avatar
AlexandruP
Frequent Visitor
3 years ago

Sales last year, same week number, same day

Hello there,

 

I am currently having current year sales and have a measure calculating last year sales. However, it is looking on the same day last year increments but I am more interested in looking in the same week number, same day number (so if it is week 42, Monday, to look last year in week 42, Monday).

 

I have managed to get the correct sales per day by creating a new collumn like this :

DayWeekYear = ([UgedagNummer]*100+ [UgeNr])*10000+[År]
 
and then a measure like this :
Oms. kr. SÅ try = CALCULATE(SUM(F_Butikssalg[Oms]),FILTER(ALL(D_Periode),D_Periode[DayWeekYear] = MAX(D_Periode[DayWeekYear])-1))
 
BUT, this works only on a day level visual. If I want it displayed as a cummul of all values in a week or years and filter a longer period, it will not take all sales per specified period but rather print a value for one day. 
 
Oms is current sales. Oms. kr. SÅ is the current measure matching the same day last year (ex. 16 Oct with 16 Oct) and Oms. kr. SÅ try is last year sales taking into account week and day number( day number is : Monday=1; Tuesday=2.....Sunday=7)

 

I want when selecting sales last year to have them dispayed looking on the same week number and day number on all levels of visualization and filtering: day/week/month/year. I have tried making the same column only with year&week but then I cannot get sales last year per day and if I create one visual and select multiple week, if i have weeks display I get correct amounts but not total and if i want only a total for filtered weeks i get again a wrong number.

 

 

 
Majority of connection in the datamodel for date are made through D_Periode using Dato which is format as dd/mm/yyyy

 

Thank you in advance.

 
 
 
Dynamic calculation
SAME PERIOD LAST YEAR
Sum & Totals

2 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi AlexandruP ,

     

    Can you share with me some screenshots of your data after hiding sensitive information and tell me what's your expected output?

    Thanks for your efforts & time in advance.

     

    Best regards,

    Yadong Fang

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

  • AlexandruP's avatar
    AlexandruP
    Frequent Visitor
    3 years ago

    Hi v-yadongf-msft ,

     

     

    Thank you for the interest. So I have the current data, but I also want to calculate different KPI comparing a certain period with the corresponding period from last year. 

     

    Let's say I want a sales IDX, I want for the last year values to take the same week number and weekday number as in the period selected. From my posted above measures I can get correct values but only for pre-determined 1-week; 1-day increments. (those are the new measure I was trying to replicate what I want)

     

    I want if I select in a visual 4 weeks all together to get the aggregate sales for last year for that time span.

    Currently if I select let's say 3-30 Oct for the last year I will get sales from 3-30 Oct

    BUT this year, this period is representing weeks 40-43 and last year it was not in exactly the same weeks so I do not get an accurate comparison. I want when looking for Last Year Sales to find the same week number and same week-day-number for no matter the time span I select.

     

     

    Also, current measure to see past sales, that look on same day of last year (16 okt 2022- looks on 16 okt 2021 NOT WHAT I AM INTERSTED IN is written like this: 

     

    Oms. kr. SÅ =
    IF (
        COUNTROWS ( D_Periode ) > 1,
        VAR max_weekday =
            WEEKDAY ( MAX ( F_Butikssalg[Dato] ), 2 )
        VAR max_date =
            CALCULATE ( DATEADD ( D_Periode[Dato], -1, YEAR ), D_Periode[Dato] = MAX ( F_Butikssalg[Dato] ) )
        VAR min_date =
            CALCULATE ( DATEADD ( D_Periode[Dato], -1, YEAR ), D_Periode[Dato] = MIN ( F_Butikssalg[Dato] ) )
        RETURN
            SWITCH (
                SELECTEDVALUE ( D_Tidsforhold[Periode] ),
                "Nuværende uge",
                CALCULATE (
                    [Oms. kr.],
                    FILTER (
                        ALL ( D_Periode ),
                        D_Periode[ÅrUge]
                            = MAX ( D_Periode[ÅrUge] ) - 100
                            && MAX ( D_Periode[UgedagIndex] ) >= D_Periode[UgedagIndex]
                            && MIN ( D_Periode[UgedagIndex] ) <= D_Periode[UgedagIndex]
                            &&  WEEKDAY ( D_Periode[Dato], 2 ) <= max_weekday
                    )
                ),
                CALCULATE (
                    [Oms. kr.],
                    FILTER (
                        ALL ( D_Periode ),
                        D_Periode[Dato] <= max_date
                            && D_Periode[Dato] >= min_date
                    )
                )
            ),
        SWITCH (
            SELECTEDVALUE ( D_Tidsforhold[Periode] ),
            "Nuværende uge",
            CALCULATE (
                [Oms. kr.],
                FILTER (
                    ALL ( D_Periode ),
                    D_Periode[ÅrUge]
                        = MAX ( D_Periode[ÅrUge] ) - 100
                        && MAX ( D_Periode[UgedagIndex] ) >= D_Periode[UgedagIndex]
                        && MIN ( D_Periode[UgedagIndex] ) <= D_Periode[UgedagIndex]
                )
            ),
            CALCULATE ( [Oms. kr.], SAMEPERIODLASTYEAR ( D_Periode[Dato] )
        )
    )
    )

     

     

    Oms. kr. - sales of this year

    Oms. kr. SÅ - sales of last year

    Relations going from period to sales are based on date written like dd/mm/yyyy

     

    To bottom line it: I want my time span (no matter the selection) for last year to be percieved as

    week-day+week-number+last-year

    AND NOT (as it currently is)

    month-day+last-year