topic Re: DAX for Week Over Week comparison between two years in DAX Commands and Tips

DAX for Week Over Week comparison between two years

cheeto92 — Mon, 01 Apr 2024 15:24:29 GMT

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')

Re: DAX for Week Over Week comparison between two years

cheeto92 — Mon, 01 Apr 2024 16:24:13 GMT

lbendlin any advice please 🙂

Re: DAX for Week Over Week comparison between two years

lbendlin — Mon, 01 Apr 2024 18:01:19 GMT

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-Forum/ba-p/963216

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/1447523

Re: DAX for Week Over Week comparison between two years

cheeto92 — Mon, 01 Apr 2024 19:19:31 GMT

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

Re: DAX for Week Over Week comparison between two years

cheeto92 — Mon, 01 Apr 2024 19:23:12 GMT

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

Re: DAX for Week Over Week comparison between two years

lbendlin — Mon, 01 Apr 2024 19:25:06 GMT

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

Re: DAX for Week Over Week comparison between two years

cheeto92 — Mon, 01 Apr 2024 20:17:10 GMT

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

Re: DAX for Week Over Week comparison between two years

lbendlin — Mon, 01 Apr 2024 20:21:57 GMT

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-Forum/ba-p/963216

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/1447523

Re: DAX for Week Over Week comparison between two years

cheeto92 — Mon, 01 Apr 2024 21:08:23 GMT

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%

Re: DAX for Week Over Week comparison between two years

cheeto92 — Tue, 02 Apr 2024 12:44:32 GMT

amitchandak talespin any advice please?

Re: DAX for Week Over Week comparison between two years

talespin — Thu, 04 Apr 2024 11:55:53 GMT

cheeto92

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

Re: DAX for Week Over Week comparison between two years

cheeto92 — Thu, 04 Apr 2024 12:40:28 GMT

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

Re: DAX for Week Over Week comparison between two years

cheeto92 — Thu, 04 Apr 2024 16:15:06 GMT

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