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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |