Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Need your help as there is no built in function readily availble in DAX for previousweek. I have requirement where for current month i need to use week no which has been passed. Week ends on Friday so till 15th November i should get 2 as weeks and when i'm on 16th November then week no value should become 3 till the end of the month. There is already relationship exists between fact table and calendar table but the problem is in fact table the date is always Friday of the last month, so when i use related ( Calendar ( weeks in month ) ) , it always returns me 5 but what i want is 2 as i have only passed 2 Fridays. Or else if somehow i could get previousweek Friday date then using Filter function i'll fetch the respective weeknum? Or any better approach to achieve this?
Solved! Go to Solution.
Hi @Anonymous ,
To create a caculated column as below.
YearMOnth = FORMAT('Table'[Date],"YYYY-MMM")
Then we can get the excepted result based on that.
Column =
VAR t =
TODAY ()
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
WEEKDAY ( 'Table'[Date], 2 ) = 5
&& 'Table'[YearMOnth] = EARLIER ( 'Table'[YearMOnth] )
&& 'Table'[Date] <= t
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)
Hi @Anonymous ,
To create a caculated column as below.
YearMOnth = FORMAT('Table'[Date],"YYYY-MMM")
Then we can get the excepted result based on that.
Column =
VAR t =
TODAY ()
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
WEEKDAY ( 'Table'[Date], 2 ) = 5
&& 'Table'[YearMOnth] = EARLIER ( 'Table'[YearMOnth] )
&& 'Table'[Date] <= t
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)
Perhaps you need to change your calendar table, so it has a column for the week number in the month of the current date.
some searching gives the following formula as an example
weekinmonth = 1 + WEEKNUM ( 'Calenda'[Date] )-WEEKNUM( STARTOFMONTH ('Calenda'[Date]))
WEEKNUM returns the week number in the year for a date
so this formula gets the week number of the first day of the current month
and subtracts that from the weeknumber of the current date
it adds one for the case when you are on the first week of the year
You could use this formula to add a calculated column to your calendar table 🙂
Help when you know. Ask when you don't!
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |