Comparison Any Year to Any Year

[rit372002]

Hello,

How do I compare a given year with any year through slicer? Something about disconnected tables, but I can’t get my head wrapped around that.

I have created basic YTD and PYTD formulas which works fine.

But users are looking to compare any year with any year through slicer.

Basically I created 6 formulas after seeing Sam’s videos: 3 for YTD and 3 for PYTD:

Same Period Last Year for Fiscal Week Nbr (weeks in sales table are non standard)

Here’s a better way to solve it. Follow these three steps. First create this measure PY Sales Setup = VAR CurrentWeek = SELECTEDVALUE( Dates[Week Number] ) VAR CurrentYear = SELECTEDVALUE( Dates[Year] ) RETURN CALCULATE( [Total Sales], FILTER( ALL( Dates ), Dates[Week Number] = CurrentWeek && Dates[Year] = CurrentYear - 1 ) ) Then this one (use this in the table) PY Sales = SUMX( SUMMARIZE( Dates, Dates[Year & Week], Dates[Week Number], "LY Sales", [PY Sales Setup] ), [LY…

1. Setup formula
2. Virtual Table
3. As the total was not showing up in the card then another virtual table.

This is for YTD and below is PYTD.

YTD Formulas:

1. YTD Sales Demand Setup = 

VAR CurrentDay =  SELECTEDVALUE( 'Date'[DAY_NAME])
VAR CurrentWeek = SELECTEDVALUE( 'Date'[FISCAL_WEEK] )
VAR CurrentYear = SELECTEDVALUE( 'Date'[FISCAL_YEAR] )
RETURN
CALCULATE ( [Sales Demand],
	FILTER ( ALL( 'Date' ),
		'Date'[FISCAL_WEEK]= CurrentWeek  && [DAY_NAME] = CurrentDay &&'Date'[FISCAL_YEAR] = CurrentYear))

2. YTD Sales Demand = 
SUMX (
    SUMMARIZE (
        'Date',
        'Date'[FISCAL_YEAR],
        'Date'[DAYS_IN_FISCAL_WEEK],
        'Date'[DAY_NAME],
        "YTD Sales", [YTD Sales Demand Setup] 
    ),
    [YTD Sales]
)

3. YTD Sales Demand Cumulative = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Date', 'Date'[FISCAL_YEAR], 'Date'[FISCAL_WEEK],'Date'[DAY_NAME], "@CY Sales", [YTD Sales Demand] ),
        "ABC", [@CY Sales]
    ),
    [@CY Sales]
)

PYTD:

1. PYTD Sales Demand Setup = 
VAR CurrentDay =  SELECTEDVALUE( 'Date'[DAY_NAME])
VAR CurrentWeek = SELECTEDVALUE( 'Date'[FISCAL_WEEK] )
VAR CurrentYear = SELECTEDVALUE( 'Date'[FISCAL_YEAR] )

RETURN
CALCULATE( [Sales Demand],
    FILTER( ALL( 'Date' ),
		'Date'[FISCAL_WEEK]= CurrentWeek && [DAY_NAME] = CurrentDay &&'Date'[FISCAL_YEAR] = CurrentYear - 1))

2. 
PYTD Sales Demand = 
SUMX (
    SUMMARIZE (
        'Date',
        'Date'[FISCAL_YEAR],
        'Date'[DAYS_IN_FISCAL_WEEK],
        'Date'[DAY_NAME],
        "LY Sales", [PYTD Sales Setup]
    ),
    [LY Sales]
)

3. PYTD Sales Demand Cumulative = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Date', 'Date'[FISCAL_YEAR], 'Date'[FISCAL_WEEK],'Date'[DAY_NAME], "@LY Sales", [PYTD Sales Demand] ),
        "ABC", [@LY Sales]
    ),
    [@LY Sales]
)

So in below picture, I want to populate whatever is selected through the “Comparison Year” slicer instead of PY numbers.

Now my question is how do I make this as dynamic so I can compare to any year?

Thanks,
RK

[Melissa]

Hi @rit372002,

You can refer to this article or provide a sample PBIX if you require more assistance.

Filtering and comparing different time periods with Power BI - SQLBI

I hope this is helpful

[EnterpriseDNA]

Thanks for working on a solution on this post @Melissa.

Hello @rit372002, did the response above solve your query?

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’

[rit372002]

Hi @Melissa ,

I think I got a solution but it was too easy so I still need to see if there are any gotchas.

Here are the steps if anyone else is interested:

1. Created a disconnect YEAR table.

2. In PYTD Setup formula: did this:

3. Now whenever I do my comparison year, it calculates it correctly.

Does it make sense? I find it too easy of a solution.

Thanks for your help,
-RK