Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.
1 REPLY 1
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.

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors