Forum Discussion
Measure for Previous year with custom date table
Hi al,
I have a table with this years sales and last year sales on which I want to filter on both Month and Week. However the weeks are based on the ISO weeknumbers and I want to compare ISO week numbers against previous year.
For your infomation below a representation of the ISO week numbers in the date table.
| Date | Normal week | ISO week numer | Month |
| 1-1-2021 | 1 | 53 | 1 |
| 2-1-2021 | 1 | 53 | 1 |
| 3-1-2020 | 1 | 53 | 1 |
| 4-1-2020 | 1 | 1 | 1 |
| 5-1-2020 | 1 | 1 | 1 |
In 2020 it looks like:
| date | normal week number | Iso week numer | month |
| 1-1-2020 | 1 | 1 | 1 |
| 2-1-2020 | 1 | 1 | 1 |
| 3-1-2020 | 1 | 1 | 1 |
| 4-1-2020 | 1 | 1 | 1 |
So if I select the ISO week number 1 want to compare the dates which are on week 1 of last year.
Fortunatly I found the formula below which does that perfectly
Hope you can help me.
Hi Anonymous ,
Modify the measure as below:
Sales Last year = VAR CurrentWeek = SELECTEDVALUE ( 'Calendar'[ISO Week Number] ) VAR CurrentYear = SELECTEDVALUE ( 'Calendar'[Year] ) VAR currentMonth = SELECTEDVALUE ( 'Calendar'[MonthNum] ) RETURN IF ( NOT ( ISFILTERED ( 'Calendar'[Year] ) ), "You didnt select a year", IF ( ISFILTERED ( 'Calendar'[Year] ), IF ( ISFILTERED ( 'Calendar'[ISO Week Number] ) && NOT ( ISFILTERED ( 'Calendar'[MonthNum] ) ), CALCULATE ( [Sales], FILTER ( ALL ( 'Calendar' ), 'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] = CurrentYear - 1 ) ), IF ( ISFILTERED ( 'Calendar'[ISO Week Number] ) && ISFILTERED ( 'Calendar'[MonthNum] ), CALCULATE ( [Sales], FILTER ( ALL ( 'Calendar' ), 'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] = CurrentYear - 1 && 'Calendar'[MonthNum] = currentMonth ) ), IF ( NOT ( ISFILTERED ( 'Calendar'[ISO Week Number] ) ) && ISFILTERED ( 'Calendar'[MonthNum] ), CALCULATE ( [Sales], FILTER ( ALL ( 'Calendar' ), 'Calendar'[MonthNum] = currentMonth && 'Calendar'[Year] = CurrentYear - 1 ) ), IF ( NOT ( ISFILTERED ( 'Calendar'[ISO Week Number] ) ) && NOT ( ISFILTERED ( 'Calendar'[MonthNum] ) ), CALCULATE ( [Sales], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = CurrentYear - 1 ) ), BLANK () ) ) ) ) ) )And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
7 Replies
- amitchandak
Super User
Anonymous , Check if this can work
Sales Last year =
VAR CurrentWeek = maxx(allselected('Calendar'[), 'Calendar'[ISO Week Number] )
VAR CurrentYear = maxx(allselected('Calendar'[), 'Calendar'[Year] )RETURN
CALCULATE( [Sales],
FILTER( ALL( 'Calendar' ),
'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] = CurrentYear - 1))Unfortunatly not. Selecting a Month gives wierd results. Weeks still works fine. Have another Idea?
v-kelly-msftCommunity Support
Hi Anonymous ,
Based on your description,I cant competely reproduce your senario,could you pls provide some sample data with expected output for a test?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hi Kelly. See the attached file. Hope you can open it.
The problem is when I select o month or only a year the current measure does not give any values. I want this measure to also work when selecting a month and year and only a year.- v-kelly-msft
Hi Anonymous ,
After checking,if you have selected a year,then you wont see an error,so modify your measure as below:
Sales Last year = VAR CurrentWeek = SELECTEDVALUE( 'Calendar'[ISO Week Number] ) VAR CurrentYear = SELECTEDVALUE( 'Calendar'[Year] ) RETURN IF(ISFILTERED('Calendar'[Year]), CALCULATE( [Sales], FILTER( ALL( 'Calendar' ), 'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] = CurrentYear - 1)), CALCULATE( [Sales], FILTER( ALL( 'Calendar' ), 'Calendar'[ISO Week Number] = CurrentWeek && 'Calendar'[Year] =YEAR(TODAY()) - 1)))+0And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
