cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## Calculate last year sales of same working days

I am trying to calculate the sales for the same working days, but from last year. I mean, for example, February 6, 2023 was the 4th working day of the month; however, the 4th working day of February 2022 was February 4th.

What I'm looking for is a table that compares this year's sales to last year's sales based on the number of working days. However, I'm having the following issue:

The problem comes when I use the DATEADD function (or SAMEPERIODLASTYEAR):

``````sales_amount_LAST_YEAR =
CALCULATE(
[_sales_amount],
)``````

This happens because my measur doesn't detect that the 4th day of the month of the PREVIOS YEAR is February 4th. However, the working_day_of_month column is perfectly calculated as I can see in the image:

Does anyone know how I could fix it?

1 ACCEPTED SOLUTION
Resolver II

It's likely that it's works properly. However, finally this is that I used in Power Query (M):

``````añadirDíaLaborableDelMes = Table.AddColumn(#"NoEsDiaLaborable", "Número de día laborable del mes", each
let
CurrentMonth = [Nombre del mes],
CurrentYear = [Año],
CurrentDate = [formatted_date],
díasLaborables = Table.SelectRows(
#"NoEsDiaLaborable",
(row) =>
row[Nombre del mes] = CurrentMonth
and row[Año] = CurrentYear
and row[formatted_date] <= CurrentDate
and not row[no_es_dia_laborable]
)
in
if [no_es_dia_laborable] = false then Table.RowCount(díasLaborables) else null``````

Thank you!!

2 REPLIES 2
Super User

Hello @powerbi2srm ,

I am not sure if I understood your requirement clearly. But see if this works for you.

I have created two columns as below and then dragged them into the visual:

``WD_Year = CONCATENATE('Table'[Working Day OF the Month],YEAR('Table'[Date]))``
``Previous Year Sales Column = LOOKUPVALUE('Table'[Sales],'Table'[WD_Year],'Table'[WD_Year]-1)``

Output looks like this:

If this solves your problem, please make this reply as solution so that others can reach fast and community can note it as solved. Thanks!!

Resolver II

It's likely that it's works properly. However, finally this is that I used in Power Query (M):

``````añadirDíaLaborableDelMes = Table.AddColumn(#"NoEsDiaLaborable", "Número de día laborable del mes", each
let
CurrentMonth = [Nombre del mes],
CurrentYear = [Año],
CurrentDate = [formatted_date],
díasLaborables = Table.SelectRows(
#"NoEsDiaLaborable",
(row) =>
row[Nombre del mes] = CurrentMonth
and row[Año] = CurrentYear
and row[formatted_date] <= CurrentDate
and not row[no_es_dia_laborable]
)
in
if [no_es_dia_laborable] = false then Table.RowCount(díasLaborables) else null``````

Thank you!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors