DAX for Week Over Week comparison between two years

[cheeto92]

Hi Everyone. I have a task where I need to calculate week over week lease renewal comparions between two years and broken down by Regions.

In my Renewal Report table I have a column named "Signed Date" where I can see that if there's a date in that row it means that there was a renewal signed. The below DAX calculcates for year over year but not week over week. I created a Calendar table that contains "Date" "Month""WeekNo" and "Year". The Calendar table has a One to Many relationship with the Renewal Report table. can someone pleaseee help! Thank you

Renewals_Current_and_Previous_FY_Percentage =

VAR Renewals_Current_FY =

    CALCULATE(

        COUNTROWS('Renewal Report'),

        FILTER(

            'Renewal Report',

            'Renewal Report'[FY] = 2024 && 'Renewal Report'[Signed Date] >= MIN('Calendar'[Date]) && 'Renewal Report'[Signed Date] <= MAX('Calendar'[Date])

        )

    )

VAR Renewals_Previous_FY =

    CALCULATE(

        COUNTROWS('Renewal Report'),

        FILTER(

            'Renewal Report',

            'Renewal Report'[FY] = 2023 && 'Renewal Report'[Signed Date] >= MIN('Calendar'[Date]) && 'Renewal Report'[Signed Date] <= MAX('Calendar'[Date])

        )

    )

VAR Total_Renewals = Renewals_Current_FY + Renewals_Previous_FY

VAR Total_Entries = COUNTROWS('Renewal Report')

[talespin]

@cheeto92 

Please share some raw data in table form that covers your scenario.

[cheeto92]

     

The excel table and the chart is what i need to recreate in Power BI. I created two measures in Power BI like this: 

Signed_Renewals_2023 =

    CALCULATE(

        COUNTROWS('Renewal Report'),

        FILTER(

            'Renewal Report',

            'Renewal Report'[FY] = 2023 &&

            NOT(ISBLANK('Renewal Report'[Renewals Signed Date]))

        )

    )

Signed_Renewals_2024 =

    CALCULATE(

        COUNTROWS('Renewal Report'),

        FILTER(

            'Renewal Report',

            'Renewal Report'[FY] = 2024 &&

            NOT(ISBLANK('Renewal Report'[Renewals Signed Date]))

        )

    )

I need to show the %ofRenewals signed this week last year vs %of Renewals signed this week of current year. This is what the bar chart is showing for the central region

[cheeto92]

Fiscal Year	Renewals Signed Date	Regions (Central, West, East)
2023	02/05/23	Central
2023	02/03/23	Central
2023	02/02/23	Central
2024	02/02/24	Central
2024	02/02/24	Central
2024	02/02/24	Central
2024	02/02/24	Central

[cheeto92]

@amitchandak @talespin any advice please?

[cheeto92]

@lbendlin any advice please 🙂

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[cheeto92]

The columns in this table is what should be used to build the report. Basically in the first week of February 2023 (FY) we had 3 renewals signed vs the first week of February 2024 (FY) we have 4 renewals signed. I need to show week over week comparison between the two fiscal years. This data should be shown as a percentage and also I want to show the week over week percentage change between them.

Fiscal Year	Renewals Signed Date	Regions (Central, West, East)
2023	02/05/23	Central
2023	02/03/23	Central
2023	02/02/23	Central
2024	02/02/24	Central
2024	02/02/24	Central
2024	02/02/24	Central
2024	02/02/24	Central

[cheeto92]

This is the outcome i need to show for all regions for week over week (2023 vs 2024)

[lbendlin]

please explain how you get the expected outcome from the sample data you posted.

[cheeto92]

I need to get a total count of signed renewals for FY23 and FY24 and then break it down by Region and then extract the Date from the Renewals Signed Date column. I then have to Divide the Renewals Signed for the week by the Total Count of Renewals Signed. I hope the data below clarifies your question:

FYI, "Advisors" is a region 

[lbendlin]

second request: Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[cheeto92]

FY23
Regions	Total Count	Signed YTD	% Signed	Last Week	% Signed Last Week	Change from Last Week
Central	729	321	44%	264	36%	8%
West	622	205	33%	153	25%	8%
East	644	258	40%	180	28%	12%
BA	185	51	28%	38	21%	7%
TOTAL	2180	835	38%	635	29%	9%

FY24
Regions	Total Count	Signed YTD	% Signed	Last Week	% Signed Last Week	Change from Last Week
Central	617	284	46%	236	38%	8%
West	530	254	48%	203	38%	10%
East	591	276	47%	245	41%	5%
BA	137	63	46%	44	32%	14%
TOTAL	1875	877	47%	728	39%	8%