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
Bhuvaraghan
Frequent Visitor

Getting previous week Value in Sales report

Hi All,

 

I am preparing a sales report where I want to show accounts which moved from one stage to another in the previous week.

WeekStageAccount NamePrevious Week
10/16/2020Proposal SubmittedABC-
10/16/2020DiscoveryXYZ-
10/23/2020Contract NegoticationABCProposal Submitted
10/23/2020DiscoveryXYZDiscovery
10/30/2020WonABCContract Negotication
10/30/2020Proposal SubmittedXYZDiscovery

 

I want to calculate the Previous week column using DAX. Any help is appreciated. If I need to create any helper columns to identify previous week also, I can do.

 

Thanks,

Bhuvaraghan

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Bhuvaraghan , Try a new column like

maxx(filter(table, [Week] = earlier([Week])-7 && [Account ] = earlier([Account ])),[Stage])

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @Bhuvaraghan ,

 

You can also create a column like so:

Column =
CALCULATE (
    MAX ( 'Table'[Stage] ),
    FILTER (
        'Table',
        'Table'[Account Name] = EARLIER ( 'Table'[Account Name] )
            && 'Table'[Week]
                = EARLIER ( 'Table'[Week] ) - 7
    )
)

 

It works the same as what @amitchandak provided.

 

 

Best regards

Icey

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

Thanks a lot, worked like a charm 🙂

amitchandak
Super User
Super User

@Bhuvaraghan , Try a new column like

maxx(filter(table, [Week] = earlier([Week])-7 && [Account ] = earlier([Account ])),[Stage])

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Thanks a lot for your help 🙂

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.