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.
Hello,
I am struggling to get an accurate result because the sales last year WTD is taking the whole week instead of the day to date.
For example, I would like to compare WTD from 14 January until 18 January versus the same period last year.
Here is my formula for the LY WTD. This will take the full week last year instead of 14th to 18th.
TOTAL SALES LY WTD =
CALCULATE (
SUM ( FACT_sales ),
FILTER (
ALL ( 'Date' ),
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )-1
&& MONTH( 'Date'[Date] ) = MONTH ( TODAY () )
&& WEEKNUM('Date'[Date]) = WEEKNUM(today())
&& 'Date'[date] <= TODAY ()
)
Many thanks for your input.
Best Regards,
Steven
Solved! Go to Solution.
Hi @Anonymous
I guess the last argument in the logical and would need to be not TODAY but the equivalent of today last year, i.e.,
'Date'[date] <= DATE ( YEAR ( TODAY () ) - 1; MONTH ( TODAY () ); DAY ( TODAY () ) )
although I believe your code could be simplified in general by using time intelligence functions or just by defining the initial and end date for the week in the current year (which I guess you already have) and just subtracting one year
Hi @Anonymous
I guess the last argument in the logical and would need to be not TODAY but the equivalent of today last year, i.e.,
'Date'[date] <= DATE ( YEAR ( TODAY () ) - 1; MONTH ( TODAY () ); DAY ( TODAY () ) )
although I believe your code could be simplified in general by using time intelligence functions or just by defining the initial and end date for the week in the current year (which I guess you already have) and just subtracting one year
Hi @AlB,
I changed the formula as per your advice and it's working well!
Thank you very much for your help
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |