Forum Discussion
DAX for Week Over Week comparison between two years
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')
12 Replies
- lbendlin
Super User
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/1447523The 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
amitchandak talespin any advice please?
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 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
