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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Comparing sales on different launch dates

Hi,

 

I want to compare products launched on products that have been launched on different dates and show the sales the first 16 weeks in a chart. I've identified the launch date with a "firstnonblank" dax code, but now I have no idea how I can go on from there. 

 

Somehow I need to set the time period prior the launch data to the first entry on the x-axis and then the launch date to the next and so on...

 

Please help!

 

Br Kent

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee


@Anonymous wrote:

Hi,

 

I want to compare products launched on products that have been launched on different dates and show the sales the first 16 weeks in a chart. I've identified the launch date with a "firstnonblank" dax code, but now I have no idea how I can go on from there. 

 

Somehow I need to set the time period prior the launch data to the first entry on the x-axis and then the launch date to the next and so on...

 

Please help!

 

Br Kent


@Anonymous

You may need a calendar table as below.

dimdate =
VAR Tbl =
    ADDCOLUMNS (
        CALENDAR ( "2016-01-01", "2017-12-31" ),
        "WeekInYear", CONCATENATE ( YEAR ( [Date] ), RIGHT ( "0" & WEEKNUM ( [Date] ), 2 ) )
    )
RETURN
    ADDCOLUMNS ( Tbl, "WeekNoOverYears", RANKX ( Tbl, [WeekInYear],, ASC, DENSE ) )

Capture.PNG

 

Then connect your products(launch date) table to this calendar table(date). To calculate the first 16 weeks sales after launch, use a measure as 

 

sales =
VAR LaunchWeekNo =
    MIN ( 'dimdate'[WeekNoOverYears] )
VAR TargetWeekNo = LaunchWeekNo + 15
RETURN
    CALCULATE (
        SUM ( 'sales table'[sales] ),
        FILTER (
            'sales table',
            'sales table'[sales date] >= MIN ( 'products table'[launch date] )
                && 'sales table'[sales date]
                    <= MAXX (
                        FILTER ( ALL ( 'dimdate' ), 'dimdate'[WeekNoOverYears] = TargetWeekNo ),
                        'dimdate'[date]
                    )
        )
    )

 

View solution in original post

2 REPLIES 2
Eric_Zhang
Microsoft Employee
Microsoft Employee


@Anonymous wrote:

Hi,

 

I want to compare products launched on products that have been launched on different dates and show the sales the first 16 weeks in a chart. I've identified the launch date with a "firstnonblank" dax code, but now I have no idea how I can go on from there. 

 

Somehow I need to set the time period prior the launch data to the first entry on the x-axis and then the launch date to the next and so on...

 

Please help!

 

Br Kent


@Anonymous

You may need a calendar table as below.

dimdate =
VAR Tbl =
    ADDCOLUMNS (
        CALENDAR ( "2016-01-01", "2017-12-31" ),
        "WeekInYear", CONCATENATE ( YEAR ( [Date] ), RIGHT ( "0" & WEEKNUM ( [Date] ), 2 ) )
    )
RETURN
    ADDCOLUMNS ( Tbl, "WeekNoOverYears", RANKX ( Tbl, [WeekInYear],, ASC, DENSE ) )

Capture.PNG

 

Then connect your products(launch date) table to this calendar table(date). To calculate the first 16 weeks sales after launch, use a measure as 

 

sales =
VAR LaunchWeekNo =
    MIN ( 'dimdate'[WeekNoOverYears] )
VAR TargetWeekNo = LaunchWeekNo + 15
RETURN
    CALCULATE (
        SUM ( 'sales table'[sales] ),
        FILTER (
            'sales table',
            'sales table'[sales date] >= MIN ( 'products table'[launch date] )
                && 'sales table'[sales date]
                    <= MAXX (
                        FILTER ( ALL ( 'dimdate' ), 'dimdate'[WeekNoOverYears] = TargetWeekNo ),
                        'dimdate'[date]
                    )
        )
    )

 

CheenuSing
Community Champion
Community Champion

Hi @Anonymous

 

Can you post some sample data in excel file and share the link.  Also what is thee final char t/ output you except.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors