Last Year table based on this year table and average days sales by DAY OF WEEK.

[DazBC]

How do I display this year in a table and have the prior year (same month) in a seperate table. In the attached image I have set the filter manually to display the YEAR MONTH fro the prior year to FEB 2024. I also need to calculate the average days count for the prior year. I seemed to get the current period correct but struggling with the prior year calculation.     

Simple put I want to see the actual day last year compared to this year (Last year 01-Feb-24 was a Thursady, this year a Saturday) 

Here are my measures for the current period.

service#ROCustomer =

CALCULATE(

    [countActual],

    dimCOA[Grouping] = "Count",

    dimCOA[Account Detail] IN { "Lab - Retail Service", "Lab - Retail Detailing" }

)

countActiveDaysPerWeekday =

CALCULATE(

    DISTINCTCOUNT(dimDates[Date]),

    FILTER(

        dimDates,

        [service#ROCustomer] > 0

    ),

    ALLSELECTED(dimDates)

)

avgROPerDay =

DIVIDE(

    [service#ROCustomer],

    [countActiveDaysPerWeekday],

    0

)

[DazBC · Accepted solution]

My issue has been resolved, I needed to use OFFSET in my date table and then base my measures off that.

[DazBC]

Thanks for your response. I have tried implementing the dax and unable to come up with the end result I require displaying the current year and last year on the same page if filetring by Month & Year.  I need it to be dynamic as it will be "impossible" to change the filters each month. I have attached a sample data .pbix file to try and help with solving the challenge.
PBIX File 

[Anonymous]

Hi, @DazBC 

I failed to open your link. Could you please follow the prompts for example files?

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DazBC]

Hopefully this link works, thanks for the heads up!

Count - Month Comparison (Same Weekday).pbix

[DataNinja777]

Hi @DazBC ,

To display this year’s data in one table and the same weekday from last year in a separate table, while also calculating the average days count for the prior year, you need to adjust your measures to correctly shift the date back one year. The first step is modifying the [service#ROCustomer] measure to retrieve data from the prior year using SAMEPERIODLASTYEAR or DATEADD.

service#ROCustomer_PY =
CALCULATE(
    [countActual],
    dimCOA[Grouping] = "Count",
    dimCOA[Account Detail] IN { "Lab - Retail Service", "Lab - Retail Detailing" },
    SAMEPERIODLASTYEAR(dimDates[Date])
)

If SAMEPERIODLASTYEAR does not work due to your data model setup, an alternative approach using DATEADD can be used instead.

service#ROCustomer_PY =
CALCULATE(
    [countActual],
    dimCOA[Grouping] = "Count",
    dimCOA[Account Detail] IN { "Lab - Retail Service", "Lab - Retail Detailing" },
    DATEADD(dimDates[Date], -1, YEAR)
)

To count the number of distinct active days for the prior year, the following measure ensures that only days with data are considered:

countActiveDaysPerWeekday_PY =
CALCULATE(
    DISTINCTCOUNT(dimDates[Date]),
    FILTER(
        dimDates,
        [service#ROCustomer_PY] > 0
    ),
    ALLSELECTED(dimDates)
)

Now, the average RO per day for the prior year can be calculated by dividing the service count by the number of active days.

avgROPerDay_PY =
DIVIDE(
    [service#ROCustomer_PY],
    [countActiveDaysPerWeekday_PY],
    0
)

For the visualization, two separate tables should be created. One should filter dimDates[Year] = 2024 to show the current year’s data, while the other should filter dimDates[Year] = 2023 to display last year’s values. If the goal is to compare weekdays directly, rather than just the same date from the prior year, a calculated column in the date table helps align the same weekday from last year.

WeekdayLastYear =
VAR CurrentDate = dimDates[Date]
RETURN
    CALCULATE(
        MAX(dimDates[Date]),
        dimDates[Year] = YEAR(CurrentDate) - 1,
        dimDates[Weekday] = WEEKDAY(CurrentDate, 2) 
    )

Using this calculated column, the prior-year table can be filtered to ensure that the weekday aligns correctly instead of simply shifting the date by a year. This approach ensures that when comparing days, February 1, 2024 (Thursday), is correctly matched with February 2, 2023 (Thursday), rather than February 1, 2023. To validate whether the prior-year mapping is correct, a quick test measure can be used:

PriorYearCheck =
SELECTEDVALUE(dimDates[Date]) & " → " & FORMAT(SAMEPERIODLASTYEAR(dimDates[Date]), "YYYY-MM-DD")

With these measures, the Power BI report will correctly display actual days for last year mapped to this year’s weekdays while ensuring accurate calculations for average counts per day. If any misalignment occurs, verifying the date table's continuity and ensuring all weekdays have corresponding prior-year matches will help fine-tune the setup.

Best regards,