Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Get previous week date

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? 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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] )
        )
    )

 

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

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] )
        )
    )

 

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
kentyler
Solution Sage
Solution Sage

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 🙂

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.