cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Need help in Continuous Sales of Item Over period

Hello Dax Experts,

 

I need help regarding my present report I am working on.

I have one Sales table in which I have around 20 columns.

 

SalesId | ItemNumber | StartDate | EndDate | DepartmentManager | State | etc....

 

Sales ID: It will be unique in the table
ItemNumber: It will be repeating as there might be multiple sales but at a time only one DepartmentManager will be buying the Item. Which at a time(for specific period as per StartDate and EndDate) only DepartmentManager will be mapped to ItemNumber.
StartDate : SalesStartDate
EndDate : SalesEndDate
DepartmentManager : Person who is buying.

 

The user wants that there should be a Date slicer on report in form of a Slider.
The user want all the 20 columns in just table visual.
There will be a Date Slicer on report.

 

User wants to see all the columns only for the Item Numbers which had continuos sales over the date period defined by user.

 

For Example : If user Select Date range from 01/Jan/2019 - 31st/Dec/2019 = 365 Days

Only those rows should come in table visual which have continuos sales throughout period (365 Days).


If there are 2 DepartmentManagers who bought the same Item within period then 4 Days gap is allowed, IF there are 3 then 2*4=8 days gap is allowed and likewise.


There might be the case that One Item number may have a StartDate and EndDate duration of 2 years also and 2 days also.

But at a Single time/day only one DepartmentManager can buy respective ItemNumber.


I am badly stuck in report and looking forward for help.
Any leads will be highly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Need help in Continuous Sales of Item Over period

Hi @Developer_2202 ,

 

We can try to create a measure and used in visual filter to meet your requirement:

 

Updated Formula:

 

Visual Control =
IF (
    DISTINCTCOUNT ( 'Calendar'[Date] )
        - CALCULATE (
            DISTINCTCOUNT ( 'Calendar'[Date] ),
            FILTER (
                'Calendar',
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[ItemNumber] IN DISTINCT ( 'Table'[ItemNumber] )
                            && 'Table'[EndDate] >= 'Calendar'[Date]
                            && 'Table'[StartDate] <= 'Calendar'[Date]
                    )
                ) > 0
            )
        )
        <= 4
            * IF (
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[DepartmentManager] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[ItemNumber] IN DISTINCT ( 'Table'[ItemNumber] )
                    )
                ) = 1,
                0,
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[DepartmentManager] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[ItemNumber] IN DISTINCT ( 'Table'[ItemNumber] )
                    )
                )
            ),
    1,
    -1
)

 

 

22.jpg23.jpg

 

 

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared?


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Need help in Continuous Sales of Item Over period

Hi @Developer_2202 ,

 

We can try to create a measure and used in visual filter to meet your requirement:

 

Updated Formula:

 

Visual Control =
IF (
    DISTINCTCOUNT ( 'Calendar'[Date] )
        - CALCULATE (
            DISTINCTCOUNT ( 'Calendar'[Date] ),
            FILTER (
                'Calendar',
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[ItemNumber] IN DISTINCT ( 'Table'[ItemNumber] )
                            && 'Table'[EndDate] >= 'Calendar'[Date]
                            && 'Table'[StartDate] <= 'Calendar'[Date]
                    )
                ) > 0
            )
        )
        <= 4
            * IF (
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[DepartmentManager] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[ItemNumber] IN DISTINCT ( 'Table'[ItemNumber] )
                    )
                ) = 1,
                0,
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[DepartmentManager] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[ItemNumber] IN DISTINCT ( 'Table'[ItemNumber] )
                    )
                )
            ),
    1,
    -1
)

 

 

22.jpg23.jpg

 

 

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared?


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Need help in Continuous Sales of Item Over period

Thank you very much!

It worked perfectly 🙂

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors