Need help in Continuous Sales of Item Over period

[Anonymous]

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.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

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
)

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,

[Anonymous]

Thank you very much!

It worked perfectly 🙂