Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @BobKoenen ,
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,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @BobKoenen ,
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,
Kelly
Did 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.
Hi @BobKoenen ,
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)))+0
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@v-kelly-msft I am Sorry that i am not clear enough but this measure still does not give me a result when only selecting a month.
I also want previous year sales when selection only a Monthnum and a Year:
Hi @BobKoenen ,
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,
Kelly
Did I answer your question? Mark my post as a solution!
@BobKoenen , 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?
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |