Calculating Latest Weeks & Comparing to Same Timeframe PY

[L1102]

Hi All,

Looking for advice, I'm trying create a filter that has lastest week options for the end user on my dashboard ie. (YTD, L4,L12,L52). I have the following dax code to call the lastest timeframes, but my measures (Prior Year, change measures etc.) don't calculate properly becuase it's not evaluating against the same lastest timeframe LY ie. L4 vs L4 PY. Is this possible to do in power BI without needing to create all new measures?

Latest Time Periods =

VAR _Today = MAX(Data_Dump_Sheet[Week End])

VAR _ThisYear = YEAR(_Today)

VAR _ThisMonth = MONTH(_Today)

VAR _ThisDay = DAY(_Today)

RETURN

    SELECTCOLUMNS(

        UNION

        (

            // L52Wks

            ADDCOLUMNS(

            GENERATE(

                SELECTCOLUMNS({"L52Wks"},"Period",[Value]),

                GENERATESERIES(

                    DATE(_ThisYear,_ThisMonth, _ThisDay -364),

                    _Today-1

                )

            ),"Axis Date",[Value]),

            // L12Wks

            ADDCOLUMNS(

            GENERATE(

                SELECTCOLUMNS({"L12Wks"},"Period",[Value]),

                GENERATESERIES(

                    DATE(_ThisYear,_ThisMonth, _ThisDay -84),

                    _Today-1

                )

            ),"Axis Date",[Value]),

            // L4Wks

            ADDCOLUMNS(

            GENERATE(

                SELECTCOLUMNS({"L4Wks"},"Period",[Value]),

                GENERATESERIES(

                    DATE(_ThisYear,_ThisMonth, _ThisDay -28),

                    _Today-1

                )

            ),"Axis Date",[Value]

            )

,

            // YTD

                GENERATE(

                SELECTCOLUMNS({"YTD"},"Period",[Value]) ,

                VAR BaseTable =

                    SELECTCOLUMNS(

                        GENERATESERIES(

                            DATE(_ThisYear, _ThisMonth -5, 1) ,

                            _Today

                            ),"D1",[Value]

                    )

                RETURN

                    SELECTCOLUMNS(

                        GENERATE(

                            BaseTable ,

                            FILTER(

                                SELECTCOLUMNS(BaseTable,"D2",[D1]) ,[D2]<=EARLIER([D1]))

                            )

                        ,"Date",[D2]

                        ,"Axis Date",[D1]

                    )

                )

            ) ,  

        "Period" , [Period] ,

        "Date" , [Value] ,

        "Axis Date" , [Axis Date]

    )

[d_m_LNK]

Instead of hard coding the year you should reference your date table to determine the current year otherwise you will need to change this every time.  For you time frame selections, is there a relationship between your Latest time periods table and your sales table?

[L1102]

@d_m_LNK , I hard code the year because my fiscal calendar starts in May and is currently fiscal year 2026, however when I import my data from it's source it has the proper calendar date. For example, my fiscal year has a date range of May 2025 - May 2026. When I calculate YOY changes I needed it to identify what year is current and what was prior year because there is a combination of both 2025 & 2026 in one fiscal year.

I've only been doing dax and power bi since september so i'm still very green. If you know a better way to evaluate my years I'm open to all suggestions.

[L1102]

Actually I found a way not to hard code the year... thanks for the tip!!

$ Sales CY =

VAR Current_Year = MAX(Promo Calendar'[Year])

RETURN

CALCULATE(sum(Data_Dump_Sheet[$ Sales]), Promo Calendar'[Year] = Current_Year)

[amitchandak]

@L1102 , You need to have the week Number or year week column in the date table. 
These will help in grouping 

Week Number = WEEKNUM([Date],2)

WeekDay = weekday([Date],2) // Monday Start user 1 for Sunday Start 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

For WOW 
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Measures 
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[L1102]

Hi @amitchandak ,

I'm not sure if grouping in this way is going to help. Right now I have the latest weeks being called with the DAX I have, but my measures that are caluclating CY & PY are by filtering on the year. I have a feeling this is where I'm getting stuck with my measure calculations. Looking to see if there is a work around for this, or do I need to create all new measures? ideally the user has the ability to choose if they want to select specific weeks in a seperate slicer or use the latest timeframes slicer depicted below. I'm just not sure how to calculate the current year and prior year to allow for this, or even if it's possible.

I've also created this calendar that i'm trying to use for time intelligence

[Ahmed-Elfeel]

Hi @L1102,

I hope you are doing well☺️❤️

Try this and tell me if it works:

Sales Current Period = 
CALCULATE(
    SUM(Data_Dump_Sheet[Sales]),
    KEEPFILTERS(Latest_Time_Periods)
)

Sales Prior Year = 
VAR SelectedPeriod = SELECTEDVALUE(Latest_Time_Periods[Period])
VAR MaxDateInContext = MAX(Latest_Time_Periods[Date])
VAR MinDateInContext = MIN(Latest_Time_Periods[Date])
VAR DayCount = DATEDIFF(MinDateInContext, MaxDateInContext, DAY) + 1
RETURN
SWITCH(SelectedPeriod,
    "YTD",
    TOTALYTD(SUM(Data_Dump_Sheet[Sales]), DATEADD('Date'[Date], -1, YEAR)),
    
    "L4Wks",
    CALCULATE(
        SUM(Data_Dump_Sheet[Sales]),
        DATEADD('Date'[Date], -1, YEAR),
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -DayCount, DAY)
    ),
    
    "L12Wks",
    CALCULATE(
        SUM(Data_Dump_Sheet[Sales]),
        DATEADD('Date'[Date], -1, YEAR),
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -DayCount, DAY)
    ),
    
    "L52Wks",
    CALCULATE(
        SUM(Data_Dump_Sheet[Sales]),
        DATEADD('Date'[Date], -1, YEAR),
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -DayCount, DAY)
    ),
    
    BLANK()
)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[L1102]

Hi @Ahmed-Elfeel , what is the KEEPFILTERS( Latest_Time_Period referring too?) I'm not sure what to link this field too? Also getting 

[FBergamaschi]

Can you please clarify

- what L4,L12,L52 mean

- how you are using this code (in a filter for a measure to use in a visual? If yes, what columns are you grouping in Rows and what is the code of the measure? Or is this a calculated table?)

- what is the data model you have, please show and comment the tables

In addition, please show an image of the result you are currently getting and what you are looking for as good result

Thanks

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[L1102]

Hi @FBergamaschi  , I'm trying have it available for the end user as a slicer, so instead of going in and clicking all the weeks they want to evaluate, insteadt they have the options to choose to evaluate YTD, L4, L52 etc. The end goal is once they choose L4, all my charts and measures calculate/update visual to L4 weeks vs L4 week same timeframe last year.

Right now I have the latest weeks being called with the DAX I have, but my measures that are caluclating CY & PY are by filtering on the year. I have a feeling this is where I'm getting stuck with my measure calculations. Looking to see if there is a work around for this, or do i need to create all new measures for this?

[d_m_LNK]

Are you applying this measure as a filter of a visual right now or how are you trying to apply this code?  It looks like you could create this as a calculated table within your model to then use with visuals and measures but you would need to build relationships to your fact with this.  You would also need to reference these dates in your measures as I don't know of a way to get around that.  

[L1102]

Hi @d_m_LNK , I'm trying have it available for the end user as a slicer, so instead of going in and clicking all the weeks they want to evaluate, insteadt they have the options to choose to evaluate YTD, L4, L52 etc. The end goal is once they choose L4, all my charts and measures calculate/update visual to L4 weeks vs L4 week same timeframe last year.

Right now I have the latest weeks being called with the DAX I have, but my measure that are caluclating CY & PY are by filtering on the year. I have a feeling this is where I'm getting stuck with my measure calculations. Looking to see if there is a work around for this, or do i need to create all new measures for this?