cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Calculate WTD sales for same week last year, but for the same number of elapsed days

Hi!

Im struggeling with a time int. measure to calculate total sales last year. The main goal is to be able to show a table with [Department], [#Sales WTD] and [#Sales WTD_LY)] - without the need of any slicers at all, where the measure it self takes into account the current weeknumber based on NOW() / TODAY().

This is working fine with some easy measures, but my main problem is that [#Sales WTD_LY] calculates the sum for the whole week last year, and not for instance: only sales from monday until thursday (todays day). This makes it quite useless a KPI.

So - does anyone know how to take this into account?

Thanks! (Mock data set = https://ufile.io/uarzc)

(My week starts on Mondays FYI )

`#SalesTotal = SUM([NetAmount])`
```#SalesTotal_LY = CALCULATE([#SalesTotal];DATEADD(Dates[Date];-1;YEAR))

```
```#SalesWTD =
CALCULATE([#SalesTotal];
FILTER(
Dates;
YEAR( Dates[Date] ) = YEAR( NOW() )
&& WEEKNUM( Dates[Date];2 ) = WEEKNUM( NOW();2)
)
)```
```#SalesWTD_LY =
CALCULATE([#SalesTotal_LY];
FILTER(
Dates;
YEAR( Dates[Date] ) = YEAR( NOW() )
&& WEEKNUM( (Dates[Date]);2 ) = WEEKNUM( NOW();2)
)
)```
1 ACCEPTED SOLUTION
Employee

@Anonymous,

Create the following columns in your Dates table.

`WeekNumber = WEEKNUM(Dates[Date],2)`
`Year = YEAR(Dates[Date])`

Then create the following measure in your sales table.

```SameWeekLastYear = IF (
HASONEVALUE ( 'Dates'[Year] )
&& HASONEVALUE ('Dates'[WeekNumber] ), CALCULATE(
sales[#SalesTotal],
FILTER (
ALL ( 'Dates' ),
'Dates'[Year] = VALUES ( 'Dates'[Year] )-1
&& 'Dates'[WeekNumber] = VALUES ( 'Dates'[WeekNumber] )
&& 'Dates'[WeekNumber] = WEEKNUM(NOW(),2 )
)
))```
Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Employee

@Anonymous,

Create the following columns in your Dates table.

`WeekNumber = WEEKNUM(Dates[Date],2)`
`Year = YEAR(Dates[Date])`

Then create the following measure in your sales table.

```SameWeekLastYear = IF (
HASONEVALUE ( 'Dates'[Year] )
&& HASONEVALUE ('Dates'[WeekNumber] ), CALCULATE(
sales[#SalesTotal],
FILTER (
ALL ( 'Dates' ),
'Dates'[Year] = VALUES ( 'Dates'[Year] )-1
&& 'Dates'[WeekNumber] = VALUES ( 'Dates'[WeekNumber] )
&& 'Dates'[WeekNumber] = WEEKNUM(NOW(),2 )
)
))```
Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors